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!



        • 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



            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) /




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