Multi-step calculation (w/ aggr)

[hobanwashburne]

I’m doing some calculations comparing a customer’s product mix to a standard model.

So far I have a chart with the product lines as the Dimension and the Expression sums the Net Sales

Expression =  sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)

ProdLine   Net

A          7,441

B          15,054

C          703

D          162

Then I need to divide the net sales of each product line by the standard model (this also functions as intended)

Expression = aggr(sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc)

ProdLine     Net  /   StdMdl         

A           7,441    0.4575   = 1,626

B           15,054   0.1275   = 118,071

C           703      0.1175   = 5,986

D           162      0.2975   = 543

Here’s what I can’t get to work. I need to select the max value (118,071) and multiple it by the standard model

ProdLine    MaxNet  *  StdMdl    NewAmt

A          118,071    0.4575    = 54,017

B          118,071    0.1275    = 15,054

C          118,071    0.1175    = 13,873

D          118,071    0.2975    = 35,126

Then I need to subtract the original net sales from the newly calculated amount.

ProdLine    NewAmt  -  StdMdl   Result

A          54,017     7,441    = 46,576

B          15,054     15,054   = 0

C          13,873     703      = 12,423

D          35,126     162      = 34,964

These last four values (46576 , 0 , 12423 , 34964) are the only values that need to be displayed on the graph.

[Carlos_Reyes]

Try:

Aggr(

(

Max( Total {< GapAnalysisFlag = {1} FiscalYear = {2013} >}

aggr(

sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)

/StdMdl

,ProdLineDesc)

)

*StdMdl)

)

- sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)

)

[MK_QSL · Accepted solution]

UPDATED

Step 1 : NetSales

Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)

Step 2: NetSales divided by StdMdl and Aggregated by ProdLineDesc for each ProdLine

Aggr(Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc)

Step 3: This will give Max value for 2nd Step

Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))

Step 4 : Individual multiply by StdMdl

StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))

Step 5: Step 4 - Actual NetSales

StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))

-

Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)

So Step 5 is your required result...

Hope this will work...

[MK_QSL]

BTW... your result of

ProdLine     Net  /   StdMdl        

A           7,441    0.4575   = 1,626

B           15,054   0.1275   = 118,071

C           703      0.1175   = 5,986

D           162      0.2975   = 543

is wrong for A....

[hobanwashburne]

Manish,

Thank you very much for your help. I am using the equasion you provided. However, I am having some trouble with a variation of it. I would like to change the "  FiscalYear = {2013} " portion of the set analysis so that it is based on the previous 6 months from the current day. I have calculated the begining date and set it equal to a variable via the script: v6MonthsAgo = AddMonths(MonthStart (Today()),-6);   I also have a Master Calendar containing the field "CalDate"

StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} CalDate >= v6MonthsAgo >} NetSales)/StdMdl,ProdLineDesc))

-

Sum({$< GapAnalysisFlag = {1} CalDate >= v6MonthsAgo >}NetSales)

How should this new equasion look?