3 Replies Latest reply: Nov 9, 2015 2:58 PM by Gopal Anandan RSS

    Calculating cumulative %

      My data is has following cols

       

      Country, State, Period(in mm/dd/yyyy format), Qty, Year

      A, A1, 1/1/2015, 10, 2015

      A, A2, 1/1/2015, 20, 2015

      A, A1, 2/1/2015, 25, 2015

      A, A1, 1/1/2014, 15, 2014

      A, A1, 2/1/2014, 10, 2014

      B, B1, 3/1/2015, 30, 2015

       

      I want to achieve the following output

       

      Year     Country     Sum(Qty) (for the Year)     Cumulative % (at  Year level)

      2015                   A           10+20+25=55                    =55/(55+30)

      2015                   B            30                                   =30/(55+30)

      2014                   A            15+10=25                        =25/(25)

       

      Any help much appreciated.

       

      Thanks,

      Gopal

        • Re: Calculating cumulative %
          Massimo Grossi

          Sum (Qty) / Sum (total <Year> Qty)

           

          1.png

            • Re: Calculating cumulative %

              Thanks! I was trying the same all this time but it wasn't working. I figured out that for the above to work, the first col. of the output HAS to be a Dimension (in the input data) and it can't be a dimension expression.

               

              Earlier my first col.  would be  calculated dimension as  Year(Period). Now with this in. the above


              Sum (Qty) / Sum (total <Year> Qty) would still calculate the % against total across all years. So I even tried


              Sum (Qty) / Sum (total <Yea(Period)r> Qty)  - even this didn't work. Finally had to resort to change my dimension from a calculated one to a direct Year dimension and that's when the formula worked.


              Is this expected behavior?

            • Re: Calculating cumulative %
              Stefan Wühl

              Create a chart with dimensions Year and Country.

               

              Then first expression

              = Sum(Qty)

              and as second

              = Sum(Qty) / Sum(TOTAL<Year> Qty)