Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
HirisH_V7
Honored Contributor

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

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

HirisH
“Aspire to Inspire before we Expire!”
1 Solution

Accepted Solutions

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

Are you looking for something like this?

Capture.PNG

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))

View solution in original post

15 Replies
MVP & Luminary
MVP & Luminary

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

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
Honored Contributor

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

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

HirisH
“Aspire to Inspire before we Expire!”
MVP
MVP

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

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

2016-01-16 #1.PNG

HirisH_V7
Honored Contributor

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

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

HirisH
“Aspire to Inspire before we Expire!”
MVP & Luminary
MVP & Luminary

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

Try these expressions:

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

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


talk is cheap, supply exceeds demand
HirisH_V7
Honored Contributor

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

Hi gwassenaar‌,

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

-Hirish

HirisH
“Aspire to Inspire before we Expire!”

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

Are you looking for something like this?

Capture.PNG

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))

View solution in original post

MVP & Luminary
MVP & Luminary

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

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.


talk is cheap, supply exceeds demand

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

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

 

YearMonthOutput
Jan-13221
Feb-13243
Mar-13276
Apr-13319
May-13863
Jun-13939
Jul-131027
Aug-131116
Sep-131192
Oct-131258
Nov-131312
Dec-131346
Jan-14123
Feb-14126
Mar-14158
Apr-14203
May-14269
Jun-14347
Jul-14446
Aug-14453
Sep-14541
Oct-141429
Nov-141496
Dec-141518
Jan-15123
Feb-15156
Mar-15189
Apr-15522
May-15554
Jun-15887
Jul-15908
Aug-15919
Sep-15931
Oct-15953
Nov-15964
Dec-151085