Rolling 12 Months chart with current month as other expression

[#1]

I am currently using this expression:

(sum([Invoice Line Amount Rev]) + sum([Misc Amount Rev]))/count(DISTINCT Employee)

to show the revenue per employee. I want to also compare on the same chart the rolling annual 12 month amount.

I have searched for rolling 12 month examples but I can not seem to get them to work. I am using a calendar table that is linking the data together.

Chart Dimension is CalendarMonthandYear displayed as Oct 2010.

How can I do this?

It should show for example of last month, amount per employee for january, and the amount per employee for Feb 2011- Jan 2011

[v_iyyappan]

Hi,

     cau u add the sample QV based on requirments .

Iyyappan

[Anonymous]

hi,

hope this help you

[#4]

This expression returns a No Data to Display error when only that expression is displayed.

I need this to calculate for 12 months before every month year displayed on the chart.

Using the same original expression and marking it as accumulate 12 steps back works, except when you select a year say 2011. If this is selected the only month that shows correctly is December because it does not pull data from 2010 for the previous months.

[#5]

How would i do this down to the day?

[#6]

Also my issue is that I need it to show for sales: Previous 12 month total for each dimension, even with a date range selected. Accumulate 12 steps works with no selection. However if someone selects Jan 2012 I still need it to sum Feb 2011 to Jan 2012.

I also need the rolling 12 month average for head count. I can not use accumulate 12 steps back because it summarizes the data instead of averaging it.

I need something like set analysis that takes into account the current dimension of month ending (MM/DD/YYYY) and then sum/avg 12 steps back.

Can someone please Help?

Edit: I relized I can just set the chart to an alternate state and read only so that the user always sees all data. This solves the issue of the values being wrong when a month or year is selected.

I am still having issues with the sum and average of sales and headcount though:

Headcount:

RangeSum( Above (count(DISTINCT Employee)),0,12) / 12

Sales:

RangeSum( Above (sum([Sales  Revenue])),0,12)

Annualized:

(RangeSum( Above (sum([Sales  Revenue])),0,12)/(RangeSum( Above (count(DISTINCT Employee)),0,12) / 12))

[#7]

I've attached my sample app, and then what my client would like to see. I will continue to use your expression to try and test mine, but any help would be greatly appreciated.

Alex

[#8]

I'm trying to work through this, any idea why this calc is not working?

I'm trying to achieve taking 2010 results and compare it to 2011, but I want the rolling 12 instead of just the whole year.

Sum({<YearMonth = {">=$(=Date(addmonths(Max(YearMonth), -24), 'MMM YY')) <=$(=Date(addmonths(Max(YearMonth), -12),  'MMM YY'))"} >} Sales)/Sum({<YearMonth = {">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM YY')) <=$(=Date(addmonths(Max(YearMonth), 0),  'MMM YY'))"} >} Sales)

The answer to the expression comes out as 1, and only shows dic 10 for month. Both pieces work fine on their own, but when brought together, I don't get the expected results.

[#9]

Alex, You might get a quicker response if you post a new discussion. People may not see your question here due to them only seeing the original question and that you posted on it

[#10]

Can anyone help me on this?