Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Re: Rolling 12 months sum

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

Highlighted
Not applicable

Re: Rolling 12 months sum

Hi Sharma,

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

Regards,

Sajeevan

View solution in original post

Highlighted
Specialist
Specialist

Re: Rolling 12 months sum

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

Have a nice long Week and enjoy...

Thanks again.

Cheers.

Highlighted
Not applicable

Re: Rolling 12 months sum

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

Highlighted
Not applicable

Re: Rolling 12 months sum

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

Highlighted
Not applicable

Re: Rolling 12 months sum

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

Regards,

Sajeevan

Highlighted
Not applicable

Re: Rolling 12 months sum

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

Highlighted
Not applicable

Re: Rolling 12 months sum

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

Highlighted
Not applicable

Re: Rolling 12 months sum

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.

Highlighted
Not applicable

Re: Rolling 12 months sum

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