Variable pre aggregates data when used on chart for all values of Dimension.

[seemawadekar]

Hi,

A variable is defined to calculate sum of last 12 months is defined as

SUM({<Monthname=, monthno= {">=$(=$(vRolling12Month))"} * {"<=$(=$(vMonthNo))"}>}sales)

Further the app needs to find out a score for the metric based on the sum of last 12 months. 

It works well when used to find the score in KPI, but when used as a measure in chart it does not slice it by the dimension while calculating the score, sums the value for all the values of dimension and then scores it. 

How can we enforce the dimension on the chart to the variable?

Thanks.

[vinieme12]

can you post some snapshots? or a sample app?

[seemawadekar]

Hi Vineeth,

Here is the statement 

IF( $(=$(vrolling12Sales))=0,0
,IF($(=$(vrolling12Sales)) <= $(=SUM({<Metric={'Sales'},Level={'1'}>}Max_Score)),MAX({<Metric = {'Sales'},Level={'1'}>}Points)
,IF($(=$(vrolling12Sales)) <= $(=SUM({<Metric={'Sales'},Level={'2'}>}Max_Score)),MAX({<Metric = {'Sales'},Level={'2'}>}Points)
,IF($(=$(vrolling12Sales)) <= $(=SUM({<Metric={'Sales'},Level={'3'}>}Max_Score)),MAX({<Metric = {'Sales'},Level={'3'}>}Points)
,IF($(=$(vrolling12Sales)) <= $(=SUM({<Metric={'Sales'},Level={'4'}>}Max_Score)),MAX({<Metric = {'Sales'},Level={'4'}>}Points)
,IF($(=$(vrolling12Sales)) <= $(=SUM({<Metric={'Sales'},Level={'5'}>}Max_Score)),MAX({<Metric={'Sales'},Level={'5'}>}Points)
))))))

Thanks

[seemawadekar]

If the variable is used by itself, it slices the total for the dimension on the chart, but when used in IF statement or set analysis, it aggregates the measure for all values of the dimension. 

[seemawadekar · Accepted solution]

Thank you all for your responses. 

Calculating the rolling 12 in load script, joining the metric table to it and then using the points from the table on the sheet worked.