Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


robert fuschetto

Recommended Posts

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

image.png.49e0604324f36af0a6b950c746c58b39.png 

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

THANKS!

Link to comment
Share on other sites

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....

image.png.a249e646f2962cf83501aef7cfa4a719.png

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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...?

image.png.0190d99b731461de8af89752d3802aaa.png 

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.

Link to comment
Share on other sites

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?

image.png.235301a98306136493ee17a8293c7d60.png 

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

Link to comment
Share on other sites

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

image.thumb.png.393e9dfdded1e891ebbdfaf562703535.png

Link to comment
Share on other sites

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

image.png.c25f8100521d9c3e06d497161a8c85fc.png

Link to comment
Share on other sites

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

image.thumb.png.01be7daa37bda4ac759463cc4729daae.png

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...