topic Rolling YTD calculation problems in QlikView

Rolling YTD calculation problems

Anonymous — Mon, 07 Jun 2010 09:24:27 GMT

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

Mon, 07 Jun 2010 13:17:02 GMT

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

Anonymous — Mon, 07 Jun 2010 15:30:42 GMT

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