Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
Is everyone down the pub???
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.
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...
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)
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
That's great... you're right it works a treat... thanks, Mike