Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Median without AGGR: Performance Issues

Hi All,

In my dashboard i am calculating median value for No of days spent at hospital for patients as per the drugs,

So the metric which i am using is :

median(aggr([No of days],Patient,Drug))

But this is really killing the performance of my dashboard.

Does anyone has any other alternative for this median calculations without using AGGR functions.

Regards

Khushboo

21 Replies
sunny_talwar

Makes sense, I think in that case you will have to use Aggr() unless you can pre-aggregate this in script (which will make this value static and I guess you would not want that as well

Not applicable
Author

Hi Sunny,

Thats my problem. If i do it at chart level i am facing performance issues.

Any alternative.

Regards

Khushboo

sunny_talwar

Don't think so

Not applicable
Author

what is we create a aggr table in script and link it to the main fact table by

Temp:

load Patient,Drug as Drug1,Time as Time1,

sum([No of Days]) as Num

Resident A group by Patient,Drug,Time;

This Temp Table will be joined to fact by patient.

Regards

Khushboo

sunny_talwar

You can do this... but remember this will be a static number which won't change based on selections anymore... is that acceptable?

Not applicable
Author

When i try to do this in my actual application, i join this temp table to original fact with Patient ID.

Its not a static value .

even now i m confused. Any thoughts on why?

sunny_talwar

It changes based on selections on the front end?

Not applicable
Author

Yes, like i have other fields in fact as well. Based on those it changes.

sunny_talwar

So this is what I mean... I added this to your script

Left Join (Table)

LOAD Drug,

  Median(Temp) as Median

Group By Drug;

LOAD Drug,

  Patient,

  Only([No of Days]) as Temp

Resident Table

Group By Drug, Patient;

Now when nothing is selected, everything looks good

Capture.PNG

But as soon as I select Time, Median(Aggr(...)) will change, but Median, which is calculated in the script won't change

Capture.PNG

Is that acceptable?

Not applicable
Author

Sunny,

But when i select Time,

my expectation is median should change.

Because for year 201701---for drug C---3.5 should be Median..

'But as we calculate in script median is only 3.

Khushboo