Aggr Variable Calc - Set Analysis

wcilliers — Fri, 12 Aug 2016 09:11:14 GMT

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.

Re: Aggr Variable Calc - Set Analysis

Gysbert_Wassenaar — Fri, 12 Aug 2016 09:44:31 GMT

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

Re: Aggr Variable Calc - Set Analysis

wcilliers — Fri, 12 Aug 2016 10:04:08 GMT

Thanks Gysbert, will give it a try.

topic Aggr Variable Calc - Set Analysis in QlikView

Aggr Variable Calc - Set Analysis

Re: Aggr Variable Calc - Set Analysis

Re: Aggr Variable Calc - Set Analysis