topic Re: YTD Calculation in QlikView

YTD Calculation

Wed, 11 Jul 2012 07:39:16 GMT

MonthId	Volume
1	20
2	21
3	24
4	25
5	22
6	33
7	23
8	43
9	34
10	32
11	28
12	30

This is the dummy data i created. I have to calculte YTD. So in july i have to calculate sum of volume of MonthId=1 to MonthId=7. and my expression has to be dynamic so that it automatically adjusts if i move to next month.

I have used this expression for calculating YTD

rangesum((sum({$<MonthId ={1}>} Volume)), (sum({$<MonthId ={7}>} Volume)))

but its not giving me the correct value. It is just summing up MonthId 1 and 7 value giving 43 as the result. But i want the value to be sum of monthId 1 to 7 that is 168. How can i do that. In the second part of the expression i will make the monthid 7 value dynamic using variable so there is no concern about that but Rangesum is not working

Please someone help me out.

I have attached a sample qvw file.

Re: YTD Calculation

Wed, 11 Jul 2012 09:07:48 GMT

solution attached

Re: YTD Calculation

Sokkorn — Wed, 11 Jul 2012 09:08:34 GMT

Hi sumitharsh,

Let try this set analysis : sum({$<MonthId ={">=1<=7"}>} Volume)

Do let me know.

Regards,

Sokkorn

Re: YTD Calculation

jagan — Wed, 11 Jul 2012 12:17:59 GMT

Hi,

Use this expression

=sum({<MonthId ={'>=1<=7'}>} Volume)

Regards,

Jagan.

Re: YTD Calculation

Wed, 11 Jul 2012 12:20:56 GMT

You could, but that would not be dynamic, what happens next month? Regards Rahul

Re: YTD Calculation

Wed, 11 Jul 2012 12:24:03 GMT

This is my final expression where vMonth is a variable which will have the values 1,2,3---12 respectively based on the month i am in. So my expression will be dynamic.

sum({$<MonthId ={">=1<=$(vMonth)"}>} Volume)

Thanks everyone for your help