topic Re: Rolling 12 months sum in QlikView

Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 08:30:20 GMT

Hi All,

I have explained the isssue in the attached QV file. Please refer to this. This is about calculating rolling 12 months sum.

Thanks for your time.

Re: Rolling 12 months sum

Anonymous — Thu, 03 Nov 2011 08:43:40 GMT

Hi,

may be this example can help you

Re: Rolling 12 months sum

Thu, 03 Nov 2011 09:08:12 GMT

Pls try this in your expression...

= Sum({$<Year={$(=max(Year))}>}Sales)

Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 09:35:39 GMT

HI Alfasierra,

Thx fr ur time.... but this solution doesnt work as per my requirement. I need to show the sum for last 12 months under for any case. So under Jan 2011, the value i should get is 47408(as per your qvw) and this is the sum from Feb 2010-Jan 2011. This way I want ti display for all the months.

Re: Rolling 12 months sum

Thu, 03 Nov 2011 10:29:26 GMT

Hi Sharma,

You need some bit of change in your data load. The reason being, you need a datestamp. The change you need is like below

Months:
mapping LOAD * INLINE [
    Month, MonthN
    Jan, 1
    Feb, 2
    Mar, 3
    Apr, 4
    May, 5
    Jun, 6
    Jul, 7
    Aug, 8
    Sep, 9
    Oct, 10
    Nov, 11
    Dec, 12
];

b:
Load *,
Month&' ' &Year as MonthYear,
MakeDate(Year,ApplyMap('Months',Month),1) as Period
Resident a;
Drop table a;

Then you can use the below expression for 12 month rolled sum of sales

RangeSum(Above(Sum({$<Year=, Month= {'>$(=MonthStart(Max(Period),-12,0))<=$(=MonthStart(Max(Period),0,0))'}>} Sales),0,12))

Hope this solves the issue.

If you are okay for including some changes to your loading then you can create a rolling period like below and use the RollingPeriod as your dimension

RollingMonths:

load Period, Period as RollingPeriod, year(Period) as RollingYear Resident b;

load Period, Addmonths(Period,1,1) as RollingPeriod, year(Addmonths(Period,1,1)) as RollingYear Resident b;

load Period, Addmonths(Period,2,1) as RollingPeriod, year(Addmonths(Period,2,1)) as RollingYear Resident b;

load Period, Addmonths(Period,3,1) as RollingPeriod, year(Addmonths(Period,3,1)) as RollingYear Resident b;

load Period, Addmonths(Period,4,1) as RollingPeriod, year(Addmonths(Period,4,1)) as RollingYear Resident b;

load Period, Addmonths(Period,5,1) as RollingPeriod, year(Addmonths(Period,5,1)) as RollingYear Resident b;

load Period, Addmonths(Period,6,1) as RollingPeriod, year(Addmonths(Period,6,1)) as RollingYear Resident b;

load Period, Addmonths(Period,7,1) as RollingPeriod, year(Addmonths(Period,7,1)) as RollingYear Resident b;

load Period, Addmonths(Period,8,1) as RollingPeriod, year(Addmonths(Period,8,1)) as RollingYear Resident b;

load Period, Addmonths(Period,9,1) as RollingPeriod, year(Addmonths(Period,9,1)) as RollingYear Resident b;

load Period, Addmonths(Period,10,1) as RollingPeriod, year(Addmonths(Period,10,1)) as RollingYear Resident b;

load Period, Addmonths(Period,11,1) as RollingPeriod, year(Addmonths(Period,11,1)) as RollingYear Resident b;

Best Regards,

Sajeevan

Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 11:08:15 GMT

HI Sanjeevan...Thanks but still its not working. If possible can you attach the working example ?

Thanks.

Rolling 12 months sum

Thu, 03 Nov 2011 11:10:44 GMT

can you attach your modified QV?

Re: Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 11:32:19 GMT

HI Sanjeevan,

I have attached the file. You can see that its not fetching any values in the bars.

Re: Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 13:20:41 GMT

The value shown against each month in dimension should be the sum of previous 12 month. Suppose we are seeing the data for Mar 2011. In that case, The value for Bar should display sum of all the values from Apr 2010 to Mar 2011. This should be happening for each and every month.

Re: Rolling 12 months sum

Thu, 03 Nov 2011 13:32:55 GMT

Hi Sharma,

Since I got busy with something, it took a while to respond. Please check it now and let me know immediately since I am having a holiday for next 5 days in Dubai.

It is showing the figures that way only. It doesn't add the previous 11 months if you select the year in list box. In order to resolve such issue you need to create the rolling periods in a different table as I suggested earlier.

Please find attached the modified QV with rolling periods created in a separate table.

Look at the dimension and the expression I have used in graph 2. This will add the previous 11 months even if you select the year from the list box since I have created the rolling period table.

Have a look at it and let me know whether this meets your requirements.

Best Regards,

Sajeevan

Re: Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 13:44:23 GMT

Thx Sajeevan. You almost got it. It is all fine if I just select the Year. But If I am selecting Month as well, the graph is showing me all the bars till that month which should not be the case. In this case it should show only one month which is selected.

e.g. If I select yesr 2011 and Month as Mar, I am getting 3 bars (for jan ,feb and march) but it should show only one bar for March

Re: Rolling 12 months sum

Thu, 03 Nov 2011 13:59:57 GMT

Hi Sharma,

This can be resolved by an if condition in the expression. Please check the expression of Graph2 in attached QV.

Regards,

Sajeevan

Re: Rolling 12 months sum

montubhardwaj — Thu, 03 Nov 2011 14:07:00 GMT

Wow...That works like charm.... Many thanks for your awsome solution.

Have a nice long Week and enjoy...

Thanks again.

Cheers.

Re: Rolling 12 months sum

Tue, 25 Sep 2012 11:21:46 GMT

Hi Sajeevan,

I've just recently started using Qlikview and have a silimiar issue to what you describe here. I have posted my query but had no response yet.

Basically, 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 the previous 11 months (so going back to July 2011). Can you tell me the best way how I can go about achieving this? I just have a list of default set statements for the date etc followed by a load statement. the data goes back to Jan 2008.

I've used your code here but it does not seem to work for me.

Regards

Adrian

Re: Rolling 12 months sum

Tue, 25 Sep 2012 11:22:00 GMT

Hi Sajeevan,

I've just recently started using Qlikview and have a silimiar issue to what you describe here. I have posted my query but had no response yet.

I've used your code here but it does not seem to work for me.

Regards

Adrian

Re: Rolling 12 months sum

Tue, 25 Sep 2012 11:27:26 GMT

Can you attach your sample QV file? I can then let you know what changes you need to do.

Regards,

Sajeevan

Re: Rolling 12 months sum

Tue, 25 Sep 2012 12:51:16 GMT

Hi Adrian,

I have license version hence it will not be an issue in opening your file.

Please name the table as Data1: prior to your current load statements.

Then, you need to create another table for the rolling period like below to get the 12 month rolling, Use the RollingPeriod as your dimensions

RollingMonths:
load Period, Period as RollingPeriod, year(Period) as RollingYear Resident Data1;
load Period, Addmonths(Period,1,1) as RollingPeriod, year(Addmonths(Period,1,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,2,1) as RollingPeriod, year(Addmonths(Period,2,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,3,1) as RollingPeriod, year(Addmonths(Period,3,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,4,1) as RollingPeriod, year(Addmonths(Period,4,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,5,1) as RollingPeriod, year(Addmonths(Period,5,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,6,1) as RollingPeriod, year(Addmonths(Period,6,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,7,1) as RollingPeriod, year(Addmonths(Period,7,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,8,1) as RollingPeriod, year(Addmonths(Period,8,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,9,1) as RollingPeriod, year(Addmonths(Period,9,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,10,1) as RollingPeriod, year(Addmonths(Period,10,1)) as RollingYear Resident Data1;
load Period, Addmonths(Period,11,1) as RollingPeriod, year(Addmonths(Period,11,1)) as RollingYear Resident Data1;

Hope this will resolve your issue.

Thanks,

Sajeevan

Re: Rolling 12 months sum

Tue, 25 Sep 2012 13:09:22 GMT

Hi Sajeevan,

When you say name the table as Data1, Do you mean the 'Months' table?

If I have 4 dimensions do I need to run the Rolling months program for each dimension (Rolling period)?

Thanks

Adrian

Re: Rolling 12 months sum

Tue, 25 Sep 2012 13:48:16 GMT

Name the table as Data1 after the Months table - where you are loading the data from excel file. You need to create the rolling period only once. The RollingMonths table will link the Data1 table on Period variable.

Re: Rolling 12 months sum

Tue, 25 Sep 2012 14:00:35 GMT

The RollingMonths table loading should be placed after the Data1 is loaded from your excel file.

for example

Data1:

Load

FROM

(ooxml, embedded labels, header is 1 lines, table is POLICY_TOTAL);

Regards,

Sajeevan