Rolling 12 months sum

[montubhardwaj]

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.

[montubhardwaj]

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

[#3 · Accepted solution]

Hi Sharma,

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

Regards,

Sajeevan

[montubhardwaj]

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

Have a nice long Week and enjoy...

Thanks again.

Cheers.

[#5]

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

[#6]

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

[#7]

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

Regards,

Sajeevan

[#8]

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

[#9]

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

[#10]

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.

[#11]

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

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;

Regards,

Sajeevan