Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

Average aggr with date drill down

Hi there,

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?

Thanks

Neil

6 Replies
hemhund2016
Contributor

Re: Average aggr with date drill down

Hi,

Try adding the date drill down group name itself  instead of date dimension in the avg aggregate expression.

Thanks,

Hemanth.

Re: Average aggr with date drill down

=Avg(Aggr(COUNT(Distinct CallID),[$(=Getcurrentfield([My Group]))]))

Where My Group is the Drill Down Group Name

EDITED:
When you write above expression, you will get some red lines (syntax error).. Please ignore them.

ndeeleysww
Contributor

Re: Average aggr with date drill down

Hi Hemantha,

Unfortunately QlikSense doesn't recognised the drill down group name in the expression

Neil


ndeeleysww
Contributor

Re: Average aggr with date drill down

Hi Manish,

That would be lovely, but unfortunately getcurrentfield isn't supported by QlikSense (yet):

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/QlikView-functions-...

Sorry!

Neil

Re: Average aggr with date drill down

Employee
Employee

Re: Average aggr with date drill down

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:

Capture.PNG

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:

Capture2.PNGCapture3.PNGCapture4.PNG