topic Re: Multi-step calculation (w/ aggr) in QlikView

Multi-step calculation (w/ aggr)

hobanwashburne — Fri, 31 Jan 2014 21:47:04 GMT

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.

Re: Multi-step calculation (w/ aggr)

Carlos_Reyes — Fri, 31 Jan 2014 22:27:37 GMT

Try:

Aggr(

(

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

aggr(

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

/StdMdl

,ProdLineDesc)

)

*StdMdl)

)

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

)

Re: Multi-step calculation (w/ aggr)

MK_QSL — Sat, 01 Feb 2014 09:57:36 GMT

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

Re: Multi-step calculation (w/ aggr)

MK_QSL — Sat, 01 Feb 2014 10:04:34 GMT

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

Re: Re: Multi-step calculation (w/ aggr)

hobanwashburne — Mon, 03 Feb 2014 18:34:26 GMT

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?