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.

[Anonymous]

Hi,

may be this example can help you

[#3]

Pls try this in your expression...

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

[montubhardwaj]

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.

[#5]

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

[montubhardwaj]

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

Thanks.

[#7]

can you attach your modified QV?

[montubhardwaj]

HI Sanjeevan,

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

[montubhardwaj]

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.

[#10]

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