4 Replies Latest reply: Jun 28, 2012 9:31 AM by Byron Van Wyk RSS

    Range Sum Iterate Over Years

    Byron Van Wyk

      Hi Guys,

       

      Please can one of you help me figure this one out.

       

      Dimension = Date

      Expression = (sum(TOTAL {$<AccountLevelDesc={'Turnover'},FinancialYear={'2013'}>}CostCentreOBGValue) -

                                    RangeSum(Above(TOTAL Sum({$<AccountLevelDesc={'Turnover'},FinancialYear={'2013'}>}CostCentreOBGValue), 1, Rowno(TOTAL))))

       

      The expression above does exactly what it should do for 2013. It takes the total Revenue Value for the Year and then subtracts the previous accumulated amount. So for example and to illustrate a little more clearly. I have this table

       

      Date,      CostCentreOBGValue

      Jan 13,   1200

      Feb 13,  1100

      Mar 13,  1000

      Apr 13,   900

      May 13,  800 

      Jun 13,   700

      Jul 13,    600

      Aug 13,   500 

      Sep 13,   400 

      Oct 13,   300

      Nov 13,   200

      Dec 13,  100

       

      When i chart this the value i see in Jan 2013 with the above expression will be sum of all values for the year  and minus any previous value that has past. So Month 1 shows 7800. Then in month 2 Jan has past which had a value of 1200, so this is minused from the previous value of 7800 giving us 6600. For month 3 Mar, the new accumulated past values is 2300 and takes this away from 7800 giving us 5500 and so on till eventually in the last month u have 100.

       

      Everything is working great except i dont want to limit this expression to a single year. I want to apply this methodology across all possible years but at the end of each financial year it starts again. So when 2014 starts it takes the next 12 months and does the same diminishing principle.

       

      Is it possible to do this. BTW the when charted it should look like a jigsaw, starts big, gets small and gets big again with the start of the new. Please can you guys let me know, been struggling with this one

       

       

      Cheers,

      Byron

        • Re: Range Sum Iterate Over Years
          Byron Van Wyk

          Anyone? I also just realised that the above calculation isnt actually correct as i am creating a stacked chart with the different business units as the other dimension, it tends to add other values when more then one business unit is selected????. If anyone knows how to do accumulation sum ranges effectively, please post

           

           

          Thanks,

          Byron

            • Re: Range Sum Iterate Over Years
              Jason Michaelides

              You'll need to remove the year portion of the set analysis (only calculated once per object) and adjust the last paramter of Above() to only go up the number of date values you have (can you use a static number - 12 instead of RowNo()?)

               

              If you can't work it out from this please post your app so we can take a look.

               

              Hope this helps,

               

              Jason

                • Re: Range Sum Iterate Over Years
                  Byron Van Wyk

                  Hey Jason.

                   

                  Struggled for a long time with this one and below is the solution that finally worked, took me an incredibly long time to get this right. Below is the code that I used. Removing financial year as you mentioned was important and adding it to the aggr function when doing the rangesum seemed to do the trick. Thanks for your help mate

                   

                   

                  =if(FinancialYear=Min(TOTAL <CostCentreName>Year(Date)),
                  Sum(TOTAL<CostCentreName>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

                  Sum(TOTAL<CostCentreName>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue)-
                  aggr(RangeSum(Above( Sum({$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),
                  1,
                  Count(CostCentreName))),
                  CostCentreName,FinancialYear))

                    • Re: Range Sum Iterate Over Years
                      Byron Van Wyk

                      I forgot to add, the above is for when you have two dimensions, CostCentre and Financial Year... If you wanted to add another level down i.e Date, you use the following code...

                       

                      =if(Date=Min(TOTAL <CostCentreName>Date),

                                Sum(TOTAL<CostCentreName,FinancialYear>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

                       

                                Sum(TOTAL<CostCentreName,FinancialYear>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue)-

                                aggr(RangeSum(Above( Sum({$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

                                                                1, RowNo(Total))),CostCentreName,FinancialYear,Date))

                       

                       

                      Cheers,

                      Byron