Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
montubhardwaj
Specialist
Specialist

Rolling 12 months sum

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.

45 Replies
montubhardwaj
Specialist
Specialist
Author

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

Not applicable

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
Specialist
Specialist
Author

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

Have a nice long Week and enjoy...

Thanks again.

Cheers.

Not applicable

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

Not applicable

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

Not applicable

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

Regards,

Sajeevan

Not applicable

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

Not applicable

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

Not applicable

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.

Not applicable

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