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.

           

          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.