Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.