topic sum(if) problem in QlikView

sum(if) problem

Tue, 03 Jul 2012 07:00:36 GMT

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

Tue, 03 Jul 2012 07:11:01 GMT

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

Tue, 03 Jul 2012 07:45:22 GMT

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.