2 Replies Latest reply: Feb 13, 2015 8:24 AM by Jeffrey Willemsen RSS

    Sum total in pivot

    Jeffrey Willemsen

      Guys,

       

      I need help to get a sum total (straight table like) in a Pivot table. I allready enabled the "Show partial sum" but this won't give the right number of totals.

       

      I think the solution is not easy and I think it needs some advanced expression skills.

       

      Situation:

      I use an input field to categorise fields

      inputfield.JPG

      These fields I use as dimension in the following tables:

      result.JPG

      In the red square I fill in a number (inputsum). This number will be devided over the categories in the first tabel. The second table shows the same numbers, only rounded. The Third table shows the remainder of the original number minus the rounded number. This table is the problem.

       

      It shows the total of the remains of the three categorised field values (test). The expression i use is:

      SUM( AGGR(
      //Original calculation
        (sum(Aggr(
        sum(Counter)/sum(TOTAL <UitwisselbareFunctie> Counter)
        *
        Sum(TOTAL <UitwisselbareFunctie> ReductieInputFunctie), Leeftijdscategorie_Afspiegelen, UitwisselbareFunctie))
        -
      //Minus rounded number
        floor(sum(Aggr(
        sum(Counter)/sum(TOTAL <UitwisselbareFunctie> Counter)
        *
        Sum(TOTAL <UitwisselbareFunctie> ReductieInputFunctie), Leeftijdscategorie_Afspiegelen, UitwisselbareFunctie))))
      , UitwisselbareFunctie, Leeftijdscategorie_Afspiegelen))
      

       

      Hope someone can help me

       

      Cheers