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

    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.



          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



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


            So simple and i missed it completely.


            Many thanks.