Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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