I'm creating a nice little reference line on a chart showing the average of calls made, by date (month).
I want to add in a date drill down to this chart, but obviously, having hard coded the date dimension into the avg aggregate the reference line doesn't change.
Does anyone know of any way around this?
=Avg(Aggr(COUNT(Distinct CallID),[$(=Getcurrentfield([My Group]))]))
Where My Group is the Drill Down Group Name
When you write above expression, you will get some red lines (syntax error).. Please ignore them.
That would be lovely, but unfortunately getcurrentfield isn't supported by QlikSense (yet):
A master item has some limitations. For example you can reference the item in a conditional expression.
I took another approach but similar. Instead of a master item , how about a conditional dimension and a conditional measure ?
If i load Year, Month, and Date in a load script like this:
Then i can use a conditional dimension like this:
if( GetPossibleCount(Year)=0 or GetPossibleCount(Year)>1, Year,
if( GetPossibleCount(Month)=0 or GetPossibleCount(Month)>1, Month,Date(Date))
And a conditional measure (to calc avgs) like this:
if( GetPossibleCount(Year)=0 or GetPossibleCount(Year)>1,
avg( aggr(sum(Sales),Year)) ,
if( GetPossibleCount(Month)=0 or GetPossibleCount(Month)>1, avg( aggr(sum(Sales),Month)),avg( aggr(sum(Sales),Date)))
The 'drill' looks like this: