Rolling 12 month '%'age

[#1]

Hello...

I've set up a dial to show % of 'Yes's as below

=(Count ({$<[CCAchievedYes/No] = {'Yes'}>} [CCAchievedYes/No]))/vPolicyCount

But now I want to include a second dial showing a rolling 12 month period (from today).

Therefore I guess whatever date selections are made (IncDate and IncQuarter) need to be ignored but other selections applied.

I tied to follow these suggestions but can't foloow the syntax  http://community.qlik.com/thread/4477

Any chance someone could explain the syntax so I can apply it to my new dial???

Many thanks, Mike

[#2]

Is everyone down the pub???

[#3]

When I have created rolling average expressions before, I have used the accumulation options on the expressions tab of a chart.

For instance to create a 14 day rolling average you might have your expression be:

=sum(Sales)/14

and then set the accumulation options for 14 steps back.

This assumes of course that you have a dimension in the chart like 'date', so I'm not sure what the impact for your dial would be.

[#4 · Accepted solution]

Alternatively you could use some kind of set analysis (assuming you have an index field in your calendar so you can cross year ends etc):

=(Count ({$<IncDate=,IncQuarter=,[Date Index]={$('<=max([Date Index])')}*{$('>(max([Date Index])-365)')},[CCAchievedYes/No] = {'Yes'}>} [CCAchievedYes/No]))

This only gives you the numerator part of your percentage calculations - you would obviously also have to adjust the denominator, but I can't see what that calculation is as you've used a variable...

Hope it helps...

[#5]

Thanks Phil... it's taken me until now to figure this out  ... but it's looking great now...

The only thing I can't figure out is the "from date"...

It all works if I hard code the date:

=count({$< IncYear=, IncMonth=, IncQuarter=, InceptionDate = {">=07/06/10"}, [CCAchievedYes/No] = {'Yes'}  >} PolicyNo)

So I thought I'd create a the rolling start date in a variable:

v12MonthsStartDate = AddMonths(today(),-12)

But when I replace the hard coded date with the variable I get zero for the count:


 

=count({$< IncYear=, IncMonth=, IncQuarter=, InceptionDate = {">v12MonthStartDate"}, [CCAchievedYes/No] = {'Yes'}  >} PolicyNo)

I guess it must be something to do with the date index you mentioned, if so would you mind explaining a little more as I didn't understand that part???

Many thanks!

<Sorry for the dup post: http://community.qlik.com/message/122275#122275)

[#6]

You just need to include your variable name as a dollar expansion, so that the expression evaluates the variable first before the set analysis:

InceptionDate = {">$(v12MonthStartDate)"},

Then I think you'll have a working expression.

Date index:

You've got quite a simple monthly calendar in your model by the sounds of things. I work in a trading period calendar (i.e. non equal sized periods) rather than a monthly calendar; all of the units of time (days, weeks, periods, quarters etc) aren't just referred to by number, but also by index.

So in year 1 of the calendar, there are weeks 1 to 52 and their week indexes are 1 to 52.

But in year 2 of the calendar, there are week numbers 1 to 52, and their week indexes are 53 to 104.

year 3  ~ indexes 105 to 156

etc

This allows dynamic set analysis which can refer backwards or forward X steps easily, and crucially allows me to cross year end points, such as with your 12 month rolling history.

I could not use your solution with my calendar, as the date and time functions built into QView (e.g. AddMonth) don't operate with a periodic (i.e. non-gregorian) calendar.

Thanks

[#7]

That's great... you're right it works a treat... thanks, Mike