Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

2 Replies
Highlighted
Not applicable

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

Highlighted
Contributor
Contributor

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