2 Replies Latest reply: Jun 7, 2010 11:30 AM by David Edwards RSS

    Rolling YTD calculation problems

      Hi All,

      I am having trouble with a specific YTD calculation required by our users.

      They would like a table broken down by month that has a monthly value and then a YTD value. I would normally try to do this using a a rangesum of all the rows above the row but the users also want a subtotal by quarter and the section breaks my calculation.

      The other option I looked at was set analysis but i've been unable to find a way of summing all months upto the current month because it refuses to ignore the current dimension of the sable when doing the sum.

      heres is an example of the sort of chart they want, any help would be really fantastic!

       

      QuarterMonthSalesYTD
      Q1January1010
      February2030
      March030
      Total3030
      Q2April2555
      May1065
      June1075
      Total4575
      Q3July1590
      August10100
      September50150
      Total75150
      Q4October10160
      November
      December
      Total10160
      YTD160160


      thanks

      Dave

        • Rolling YTD calculation problems

          Hi Dave

          Test this script

          Values1:

          Load * Inline [

          Year,Month,Value

          2008,1,10

          2008,2,20

          2008,3,10

          2008,4,35

          2008,5,20

          2008,6,10

          2008,7,15

          2008,8,10

          2008,9,50

          2008,10,10

          2008,11,20

          2008,12,10

          2009,1,10

          2009,2,20

          2009,3,0

          2009,4,25

          2009,5,10

          2009,6,10

          2009,7,15

          2009,8,10

          2009,9,50

          2009,10,10

          2009,11,0

          2009,12,0

          ];

           

          Values2:

          Load Year,Month,Value,

          If(Year = Previous(Year), Value + Peek('YTD',-1,'Data'), Value) As YTD

          Resident Values1

          Order by Year,Month;

           

          Drop Table Values1;

           

          Quarter:

          Load * Inline [

          Month,MonthName,Quarter

          1,jan, Q1

          2,feb, Q1

          3,mar, Q1

          4,apr, Q2

          5,may, Q2

          6,jun, Q2

          7,jul, Q3

          8,aug, Q3

          9,sep, Q3

          10,oct, Q4

          11,nov, Q4

          12,dec, Q4];

           

          Then you can create this pivot

           

          Year

          Quarter

          MonthName

          sum(Value)

          max(YTD)

          2009

          Q1

          jan

          10

          10

          feb

          20

          30

          mar

          0

          30

          Total

          30

          30

          Q2

          apr

          25

          55

          may

          10

          65

          jun

          10

          75

          Total

          45

          75

          Q3

          jul

          15

          90

          aug

          10

          100

          sep

          50

          150

          Total

          75

          150

          Q4

          oct

          10

          160

          nov

          0

          160

          dec

          0

          160

          Total

          10

          160

          Total

          160

          160



          Anders

            • Rolling YTD calculation problems

              Hi Anders,

              Thanks for your response. I can see that this would work assuming my data was in date order. I think I would also need to order by all my other key columns though to ensure all further dimensionality still works?

              I 'm hoping for a more dynamic solution if that's possible because there are actually multiple measures that I will need to perform these calculations on and I would also like them to stay consistent when filtering by various other dimensionality.

              the sort of calc i'd like is:

              FOR YearMonth on this row of chart

              sum(value)

              where year=Year of this row on the chart

              and YearMonth <= YearMonth on this row of chart

               

              Is that sort of calc possible in qlikview?

              Thanks

              Dave