2 Replies Latest reply: Jul 3, 2012 3:45 AM by Peter Terstall

# sum(if) problem

I created a qlikview with a pivot table.

dimension 1  dimension 2   sales(with set analysis selections)  bonus 7.5% of sales

Sales = a calculated expression where i with set analysis only the sales take of the last 6 month.

In the bonus expression i try this

if(demension 2 <> 'value',[sales] * 0.075,0)

This works great. However it calculates on totals with the by the if statement excluded ( <> 'value') included.

Something like this

dimension 1  aa   € 100.00  7.5

dimension 2  bb   € 200.00     0    excluded with if statement

Total                    € 300.00 22.50 (instead of 7.5)

I tried to put a sum around [sales]*0.075 but get an error on it.

Please help me to get the correct total.

• ###### Re: sum(if) problem

This could be solved in straight table with the total mode (in expression tab) as sum of rows. In pivot table probably we need do the following for bonus.

=0.075*Sum({<dimension2-={'value'}>} Sales) and other set analysis parameters used in sales.

Regards,

Kiran Rokkam.

• ###### Re: sum(if) problem

Today i learned the solution. Many thanks for all your advice.

i put the *0.075 into the set analysis used by sales. and append an -= selection

sum

({\$<Jaar={'\$(Rapportjaar)'},Maand={'<=\$(Rapportmaand)'}, KeyGrootboek -= {'220'}>} VABTTB * 0.075)

So simple and i missed it completely.

Many thanks.