7 Replies Latest reply: Nov 21, 2017 4:42 AM by Sunny Talwar RSS

    Calculated subtotals

    Mike Slottje

      Hi experts,

       

      in the attached example file, you'll find the problem I'm facing.

      I want to create subtotals in a pivot, based on expressions with adding, subtracting, multiplying and dividing.


      I'm not sure how to create these subtotals in a pivot table.

      Should I create a dimension in the load script, in set analysis or should I create a new dimension as master dimension?


      It would be great if you guys could help me out on this one!


      Cheers,

      Mike

        • Re: Calculated subtotals
          Sunny Talwar

          Where are you hoping to see these subtotals? I am not 100% sure I understand your requirement completely

          • Re: Calculated subtotals
            bruno bertels

            Hi

             

            May be add first calculated dimension in the script :

             

            =if(WildMatch(Number,'1','2','3','3B'), 'Field 1',

            if(WildMatch(Number,'21','4'), 'Field 2',

            if(WildMatch(Number,'97','89'), 'Field 3',

            if(WildMatch(Number,'21','21A','89','97','4','4A'), 'Field 4'

            )))) as FIELDS,

             

            then in a straight table ( not pivot table )

             

            add as dimension FIELDS

             

            then as mesure :

             

            =if(WildMatch(FIELDS,'Field 1'), sum(${Number=('1','2','3','3B')}Amount),

            if(WildMatch(FIELDS,'Field 2'), sum(${Number=('21')}Amount)/sum(${Number=('4')}Amount)*100,

            if(WildMatch(FIELDS,'Field 3'), sum(${Number=('97')}Amount)-sum(${Number=('89')}Amount),

            if(WildMatch(FIELDS,'Field 4'), sum(${Number=('21','21A','89','97')}Amount) /

            sum(${Number=('4','4A',)}Amount)*100

            ))))

             

            Not sure it works , i can't test it in your app