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

# 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!

 Quarter Month Sales YTD Q1 January 10 10 February 20 30 March 0 30 Total 30 30 Q2 April 25 55 May 10 65 June 10 75 Total 45 75 Q3 July 15 90 August 10 100 September 50 150 Total 75 150 Q4 October 10 160 November December Total 10 160 YTD 160 160

thanks

Dave

• ###### Rolling YTD calculation problems

Hi Dave

Test this script

Values1:

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:

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

Resident Values1

Order by Year,Month;

Drop Table Values1;

Quarter:

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