topic Re: Rolling 12 month values in QlikView

Rolling 12 month values

Tue, 25 Sep 2012 10:14:18 GMT

Hi there,

I've just started using Qlikview and I need to sum values by year and month over a 12 month period.So for example I have data for June 2012 and I want to sum this value and previous 12 months (so going back to July 2011). Can anyone tell me how I can do this? I saw a post on here where the guy made changes to the date info script. In my code I just have a list of default set statements for the date etc followed by a load statement.

Many thanks

Adrian

Re: Rolling 12 month values

Tue, 25 Sep 2012 11:30:31 GMT

Hi laffeyboy,

You can try to use set analisys expresion like that

sum({1<[Date] = {">=$(Date-365)"}-{"<[Date]"}>} Amount)

I hope to help you.

Re: Rolling 12 month values

Tue, 25 Sep 2012 11:51:45 GMT

Hi Gregorio,

I've tried that and does not seem to work. The 'Amount' variable is the variable I want to sum, correct?

Re: Rolling 12 month values

Tue, 25 Sep 2012 12:04:22 GMT

Yes Amount is the variable to sum, I try it in one of my qlikview repots and it´s work.

Re: Rolling 12 month values

Anonymous — Tue, 25 Sep 2012 12:43:44 GMT

In the chart below the expressions you will have no accumlate , full accumlation & accumlate . Enable Accumlate and give number of steps as 12 because rolling 12 months. see the screen shot for your reference. Hope that helps.

Re: Rolling 12 month values

Tue, 25 Sep 2012 13:57:22 GMT

The proble of viany solucion is that accumulate in every dimension value, if you use time dimensions the value grows every month, for example,

Accumulate value

Jun 10 10

Feb 20 30

Mar 2 32

Re: Rolling 12 month values

Anonymous — Tue, 25 Sep 2012 14:14:21 GMT

Hi Laffyboy & Gregoria, if the above post does not satisfy the requirement then its better to do the aggregation at the backend . I mean in the fact table.

Re: Rolling 12 month values

Tue, 25 Sep 2012 15:43:16 GMT

Hi Vinay,

I've tried that and it does not work. It just sums for 2012 but not back as far as July 2011.

Regards

Adrian

Re: Rolling 12 month values

Anonymous — Tue, 25 Sep 2012 15:53:26 GMT

can you post any sample data and with sample application that would give us better understanding on what dimensions you are using and how the data is present in the application.