Aggr Variable Calc - Set Analysis

[wcilliers]

Hi Experts,

I am struggling to use a aggregated calculation in a variable with set analysis. If someone could please try and assist I would appreciate it.

Scenario:

Customers gets assigned a RebateLevel(1,2 or 3) depending on monthly average sales (This is done in a variable as follows...)

vRebateLevelCalc

if(aggr(sum({<Fisc.Year = {$(Max Year)},RebateDivision={'Division1'}>}Total <Customer>  INCOME) / Count ({<Fisc.Year = {$(Max Year)}>}TOTAL DISTINCT Fisc.Month),Customer) >4000,if(aggr(sum({<Fisc.Year = {$(Max Year)},RebateDivision={'Division1'}>}Total <Customer>  INCOME) / Count ({<Fisc.Year = {$(Max Year)}>}TOTAL DISTINCT Fisc.Month),Customer) <9999,'1'

  ,if(aggr(sum({<Fisc.Year = {$(Max Year)},RebateDivision={'Division1'}>}Total <Customer>  INCOME) / Count ({<Fisc.Year = {$(Max Year)}>}TOTAL DISTINCT Fisc.Month),Customer) >10000,if(aggr(sum({<Fisc.Year = {$(Max Year)},RebateDivision={'Division1'}>}Total <Customer>  INCOME) / Count ({<Fisc.Year = {$(Max Year)}>}TOTAL DISTINCT Fisc.Month),Customer) <19999,'2'

  ,if(aggr(sum({<Fisc.Year = {$(Max Year)},RebateDivision={'Division1'}>}Total <Customer>  INCOME) / Count ({<Fisc.Year = {$(Max Year)}>}TOTAL DISTINCT Fisc.Month),Customer) >20000,'3','4')))))

Now, in a straight table I want to list the customers, and by using set analysis I want to calculate the (sales * rebate%) where [Cust Rebate Level] = vRebateLevelCalc

This is my expression:

sum({<RebateLevel={'$(=$(vRebateLevelCalc))'},RebateDivision={'Division1'}, RebateIndicator = {1}>} INCOME*RebatePercentage)

This seems to work, but only when I have ONE customer selected. If I want to display all customers it calculates ZERO. Furthermore, If I add the vRebateLevelCalc alone as another expression, it does seem to calculate per customer. But for some reason it doesnt calculate the above expression for multiple customers.

Does anyone have an idea of what I can do to overcome this problem?

Thanks in advance!

Wynand.

[Gysbert_Wassenaar · Accepted solution]

The set is calculated at the chart level, not at the row (i.e. customer) level. I think you'll have to remove from RebateLevel={'$(=$(vRebateLevelCalc))'} the set modifier and use an if statement instead:

sum({<RebateDivision={'Division1'}, RebateIndicator = {1}>} If(RebateLevel=$(vRebateLevelCalc), INCOME*RebatePercentage))

[wcilliers]

Thanks Gysbert, will give it a try.