2 Replies Latest reply: Apr 18, 2018 10:48 AM by Marcus Steggall RSS

    Accumulation percentage based on 12 month totals!

    Marcus Steggall

      Afternoon, hope all are well

       

      I am trying to do a cumulative bar graph, across a rolling 12 months!

       

      On the Expressions tab, it's easy to change each expression to "Accumulate" 12 steps (months) back.

      However for the final Profit %, this won't work as is; percentages just become greater.

       

      Even if you did no accumulation on the expressions tab, this would only do the calculation for the month only.

       

      As you can see on the two straight tables, the 2018 Apr percentage is 53% which is correct for the month.

      However a cumulative percentage for last 12 months to 2018 Apr, should be 34%.

       

      How can I get that percentage working correctly, based on 12 month totals, at the month selected?

       

      Any help or advice, appreciated

        • Re: Accumulation percentage based on 12 month totals!
          Sunny Talwar

          Try this with no accumulation

           

          =RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


          Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

          or

          round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Profit), 0, 12))



          /


          RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


          Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

          or

          round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Revenue), 0, 12))

           

          Capture.PNG