topic Re: Rolling 12 Months chart with current month as other expression in QlikView

Rolling 12 Months chart with current month as other expression

Wed, 29 Feb 2012 21:59:22 GMT

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

Rolling 12 Months chart with current month as other expression

v_iyyappan — Thu, 01 Mar 2012 05:58:00 GMT

Hi,

cau u add the sample QV based on requirments .

Iyyappan

Re: Rolling 12 Months chart with current month as other expression

Anonymous — Thu, 01 Mar 2012 10:19:02 GMT

hi,

hope this help you

Rolling 12 Months chart with current month as other expression

Thu, 01 Mar 2012 11:48:49 GMT

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.

Rolling 12 Months chart with current month as other expression

Thu, 01 Mar 2012 21:03:03 GMT

How would i do this down to the day?

Re: Rolling 12 Months chart with current month as other expression

Fri, 02 Mar 2012 13:14:08 GMT

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

Re: Rolling 12 Months chart with current month as other expression

Fri, 02 Mar 2012 17:07:02 GMT

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

Re: Rolling 12 Months chart with current month as other expression

Fri, 02 Mar 2012 17:33:41 GMT

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.

Re: Rolling 12 Months chart with current month as other expression

Fri, 02 Mar 2012 18:22:16 GMT

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

Re: Rolling 12 Months chart with current month as other expression

Mon, 05 Mar 2012 16:31:57 GMT

Can anyone help me on this?