**CLOSED...Running total in not working in App Studio

[robert fuschetto]

Ugh. First, I have not done one of these in some time. Second, I do not recall if one is to use a DEFINE or COMPUTE. I did find some references in the forum but I am having no luck. I'll demo with the CAR file:

DEFINE FILE CAR

DEF_RUN_TOT/D12.2=LAST CAR.BODY.SALES + CAR.BODY.SALES;

END

TABLE FILE CAR

SUM

   CAR.BODY.SALES

   CAR.BODY.DEF_RUN_TOT

   COMPUTE COMP_RUN_TOT/D12.2 = LAST CAR.BODY.SALES + SALES;

BY CAR.COMP.CAR

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

   INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

 

Neither the DEFINE or COMPUTE appear to work. Seems so simple. Where am I going wrong!

THANKS!

[Patrick Huebgen]

Simple change

COMPUTE COMP_RUN_TOT/D12.2 = LAST COMP_RUN_TOT + SALES;

[robert fuschetto]

OMG...DUH on my part!😫

THANK!

[Patrick Huebgen]

But there'a a better one

TABLE FILE CARSUM CAR.BODY.SALES COMPUTE TOTAL/D12.2M = PARTITION_AGGR(SALES, TABLE, B, C, SUM);BY CAR.COMP.CARON TABLE SET PAGE-NUM NOLEADON TABLE SET ASNAMES ONON TABLE NOTOTALON TABLE PCHOLD FORMAT HTMLON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONEND

[Patrick Huebgen]

PARTITION_AGGR in general is very useful for many other use cases

[Mat Barnard]

I came here to say this. You're too quick @Patrick Huebgen​  :-)

[Mat Barnard]

Yeah, I've got a few thousand lines of code for a customer that uses 6 month rolling averages that I'll rewrite using PARTITION_AGGR one day. 😂

[robert fuschetto]

What are the parameters for PARTITION and might I need it for this?

TABLE FILE TBLMSTR_BUDG_STATSBYDAY

SUM 

   COMPUTE RUN_TOT/D12 = LAST RUN_TOT + TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.ACTUNITS; AS 'Runing ,Actual,Units'

   TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.RNBUDGUNITS/D20 AS 'Running ,Adj Budget,Units'

BY HIGHEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.CPTTYPE

ACROSS LOWEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.DATE/YYMD

WHERE ( TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.PERIOD EQ 202311 );

So using the across works...going across but going down it sort of does not reset itself when the CPT Type changes...the running total sort of applies in 'both' directions.....sorry if I confused everyone.

Ideally it would show each day of the month across, creating a running total for Actuals, (budget is already a running total) and having the different cpt types going down...but resetting the actual running total and applying by day....

[Patrick Huebgen]

Instead of TABLE you can use a sort field

to explain

PARTITION_AGGR(SALES, TABLE, B, C, SUM);

SALES is the field you want to aggregate

TABLE is based on which sort field you want to reset the aggregation - TABLE means for the full TABLE but you could use any BY field

B means from the Beginning (could be a numeric value liek -1)

C means up to the current value

Please check the functions manual for samples

here's one more for CAR

TABLE FILE CARSUM CAR.BODY.SALES NOPRINT COMPUTE TOTAL/D12.2M = PARTITION_AGGR(SALES, COUNTRY, B, C, SUM);BY COUNTRYBY CARACROSS SEATSON TABLE SET PAGE-NUM NOLEADON TABLE SET ASNAMES ONON TABLE NOTOTALON TABLE PCHOLD FORMAT HTMLON TABLE SET HTMLEMBEDIMG ONON TABLE SET HTMLCSS ONEND

[robert fuschetto]

I will play with this...THANKS!

[robert fuschetto]

that worked! But I am getting greedy....now I want not only to group by CPT TYPE but also Company. If I just add a By Company before CptType..it works but does not reset on company....can the partition have multiple levels...?

 

SUM 

   COMPUTE RUN_TOT/D12 = PARTITION_AGGR(TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.ACTUNITS, TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.COMPANY, B, C, SUM); AS 'MTD,Actual,Units'

   TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.RNBUDGUNITS AS 'MTD,Budget,Units'

BY LOWEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.COMPANY

BY HIGHEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.CPTTYPE

ACROSS LOWEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.DATE/YYMD

WHERE ( TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.PERIOD EQ 202311 );

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

   INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

   ACROSSCOLUMN=N3,

   BORDER-RIGHT=LIGHT,

   BORDER-RIGHT-STYLE=SOLID,

   BORDER-RIGHT-COLOR='BLACK',

$

TYPE=TITLE,

   ACROSSCOLUMN=N3,

   BORDER-RIGHT=LIGHT,

   BORDER-RIGHT-COLOR='BLACK',

$

ENDSTYLE

END

Thanks again.

[Patrick Huebgen]

Create an invisible (NOPRINT) DEFINE field that combines all fields that you want to use for partition ?

[robert fuschetto]

This is starting to feel like SQL...I will give it a go!

[robert fuschetto]

This has worked well until now...I have a situation where I have Company going down and Type going across. Looking down the report are running totals working great...they reset on COMPANY (I only show one company in the snip below but when the company flips---it resets!)

However going across is a runing total as well..and I do not want that. Example: On the 1st Procedure MTD Actual should be 1643 however it shows 12855 which is the MTD Visit Number 11212 + 1643. The same thing happens for Procedures.

I am so close....can this be fixed?

 

TABLE FILE TBLMSTR_BUDG_STATSBYDAY

SUM 

 COMPUTE RUN_TOTACT/D12 = PARTITION_AGGR(TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.ACTUNITS, TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.COMPANY, B, C, SUM); AS 'MTD,Actual,Units'

   

COMPUTE RUN_TOTBUD/D12 = PARTITION_AGGR(TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.BUDGUNITS, TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.COMPANY, B, C, SUM); AS 'MTD,Budget,Units'

BY LOWEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.COMPANY

BY LOWEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.DATE/YYMD

ACROSS HIGHEST TBLMSTR_BUDG_STATSBYDAY.TBLMSTR_BUDG_STATSBYDAY.CPTTYPE

[Patrick Huebgen]

Can you create a sample with wf_retail please and share what you are trying to achieve?

[robert fuschetto]

how about this - I think it illustrates my issue. Running totals seem to work by region going DOWN the report; resetting at the product level. HOWEVER...Running totals are also going ACROSS the report by Region..we want them to reset for each region going across....

TABLE FILE GGSALES

SUM 

   COMPUTE RUN_TOT_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.UNITS, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'ActUnits'

COMPUTE RUN_TOT_BUDUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.BUDUNITS, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'BudUnits'

BY GGSALES.SALES01.PRODUCT

BY GGSALES.SALES01.DATE/MDYY

ACROSS LOWEST GGSALES.SALES01.REGION

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

   INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

[Patrick Huebgen]

 Are you referring to

COMPUTE RUN_TOT_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.UNITS, GGSALES.SALES01.REGION, B, C, SUM); AS 'ActUnits2'

[robert fuschetto]

I am not sure I follow you>

I am really hoping something like this is not the answer....though it seems to work...I'd have to add more defines for the Budget for each Region. If a new REGION ever appeared you would need to remember to update the code....yuck-o-la.

DEFINE FILE GGSALES

WESTREGION/D12=IF GGSALES.SALES01.REGION EQ 'West' THEN GGSALES.SALES01.UNITS ELSE 0;

NEREGION/D12=IF GGSALES.SALES01.REGION EQ 'Northeast' THEN GGSALES.SALES01.UNITS ELSE 0;

SEREGION/D12=IF GGSALES.SALES01.REGION EQ 'Southeast' THEN GGSALES.SALES01.UNITS ELSE 0;

MWESTREGION/D12=IF GGSALES.SALES01.REGION EQ 'Midwest' THEN GGSALES.SALES01.UNITS ELSE 0;

END

TABLE FILE GGSALES

SUM

   COMPUTE RUN_TOT_WREG_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.WESTREGION, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'West_Units'

COMPUTE RUN_TOT_NEREG_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.NEREGION, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'NE_Units'

   COMPUTE RUN_TOT_SEREG_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.SEREGION, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'SE_Units'

COMPUTE RUN_MWTOT_WREG_ACTUNITS/D12 = PARTITION_AGGR(GGSALES.SALES01.MWESTREGION, GGSALES.SALES01.PRODUCT, B, C, SUM); AS 'MidWest_Units'

BY GGSALES.SALES01.PRODUCT

BY GGSALES.SALES01.DATE/MDYY

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

   INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

TABLE FILE car

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,$

ENDSTYLE

END

[David Beagan]

If I understand you correctly, I think it can be accomplished using a hold file, perhaps you have to use a hold file.

SET PAGE=NOLEAD,STYLE=WARMTABLE FILE GGSALESSUM COMPUTE ActUnits/P12 = PARTITION_AGGR(UNITS, REGION, B, C, SUM); BudUnits/P12 = PARTITION_AGGR(BUDUNITS, PRODUCT, B, C, SUM); BY PRODUCTBY REGIONBY DATE/MDYYON TABLE HOLD AS ADATAEND TABLE FILE ADATASUM ActUnits BudUnitsBY PRODUCTACROSS REGIONBY DATE/MDYYEND

[robert fuschetto]

THANKS!!!