How to create an accumulation for every 12 months in 3 years graph

[HirisH_V7]

Hi Qlikers,

I want to create an accumulation for every 12 months in Combo Chart such that for example,

2012- Jan -Dec

2013- Jan -Dec

2014- Jan -Dec

for yearly comparison.

-Thanks In Advance,

Please find attachment,

Hirish

[stevedark]

Hi Hirish,

All you need to do is create a Month and Year version of your date field in the load script, I presume you already have a Month one, but I will show that for completeness also:

LOAD

    OrderDate,

    Month(OrderDate)   as Month,

    Year(OrderDate)  as Year,

    Date(MonthStart(OrderDate), 'MMM-YY') as [Month Year]

    ...

When you reload you will then have a number of date dimensions.  If you add the Year dimension to the chart with the same expression you should get what you are after.  If you add both Year and Month you can get a line for each year comparing month on month.

If you are talking rolling month accumulations, such as a MAT value, then this blog post will help - but I think what you require is much simpler:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/‌

Hope that helps,

Steve

[HirisH_V7]

Hi stevedark,

I already created a graph for 3 years but i want to acheive accumulation for every 12 months,If i keep 12 months steps means its effecting other years too.

PFA,

Hirish

[petter]

Is it simply this you are looking for - one of the two graphs shown here?

[HirisH_V7]

Hi petter-s‌,

Nope not like that. I want to see all 36 months (3 years) and in that every 12 months should be cumulative .

Like the attached application above.

1-12 Months accumulated,

12-24 Months accumulated,

24-36 Months accumulated.

Thanks for your response,

Please help on it,

-Hirish

[Gysbert_Wassenaar]

Try these expressions:

rangesum(above(Sum(Sales),0,MonthNum))

rangesum(above(Sum(Sales)/2,0,MonthNum))

[HirisH_V7]

Hi gwassenaar‌,

Nope not working .i need to have accumulated result for every 12 months of the years.

-Hirish

[sunny_talwar · Accepted solution]

Are you looking for something like this?

Expression:

RangeSum(Above(Sum(Sales), 0, (RowNo(TOTAL) - (Year - Min(TOTAL Year))* 12)))

I guess I over-complicated the expression, Gysbert's expression seems to do the same thing:

=Rangesum(Above(Sum(Sales),0,MonthNum))

[Gysbert_Wassenaar]

Well, you've lost me completely. You say that enabling accumulation with 12 steps on the expression doesn't work. So rangesum(above(sum(Sales),0,12)) is not what you want. And the year-to-date isn't what you want either. So you'll have to explain what it is that you do want. Post a table with the results you expect.

[sunny_talwar]

Not sure, but my guess is that he is looking for this:

YearMonth	Output
Jan-13	221
Feb-13	243
Mar-13	276
Apr-13	319
May-13	863
Jun-13	939
Jul-13	1027
Aug-13	1116
Sep-13	1192
Oct-13	1258
Nov-13	1312
Dec-13	1346
Jan-14	123
Feb-14	126
Mar-14	158
Apr-14	203
May-14	269
Jun-14	347
Jul-14	446
Aug-14	453
Sep-14	541
Oct-14	1429
Nov-14	1496
Dec-14	1518
Jan-15	123
Feb-15	156
Mar-15	189
Apr-15	522
May-15	554
Jun-15	887
Jul-15	908
Aug-15	919
Sep-15	931
Oct-15	953
Nov-15	964
Dec-15	1085