21 Replies Latest reply: Jul 4, 2017 7:19 PM by Sunny Talwar

# 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

• ###### Re: Median without AGGR: Performance Issues

Do you have duplicate rows for No of days for a combination of Patient and Drug? This alone isn't working? Median(No of days])

• ###### Re: Median without AGGR: Performance Issues

No this alone wont work because we need median at patient level for each product.

• ###### Re: Median without AGGR: Performance Issues

Do you have a sample you can share?

• ###### Re: Median without AGGR: Performance Issues

If you have a table with Patient and Drug as dimensions, then the simple =Median([No of days]) will do what you require.

If you use a straight table, you can then decide how to handle the total (an expression total is probably wrong, or you can sum/average/... the rows, or have no total).

• ###### Re: Median without AGGR: Performance Issues

Hi Jonathan,

No we cant use patient as a dimension.

We have only Drug as a dimension.

• ###### Re: Median without AGGR: Performance Issues

I am not even sure what you are trying to get and that is why I recommend showing an example out of QlikView to explain what is that you are looking to get.

• ###### Re: Median without AGGR: Performance Issues

Hi Sunny,

PFA attached sample qvw for median .

Let me know if there is a way to avoid AGGR.

• ###### Re: Median without AGGR: Performance Issues

Not sure if you don't have an inner aggregation in your real scenario, but this isn't even right... take a look at this

A is associated with two patients with two different NoOfDays.... and since the no of days don't match, the inner part of your expression is 0 for Drug A. Is this really what you want?

• ###### Re: Median without AGGR: Performance Issues

All i want is to calculate median value for No of days spent at hospital for patients as per the drugs.

But patients should be inner aggregation . Patient is not a dimension in straight table.

• ###### Re: Median without AGGR: Performance Issues

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

• ###### Re: Median without AGGR: Performance Issues

Hi Sunny,

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

Any alternative.

Regards

Khushboo

• ###### Re: Median without AGGR: Performance Issues

Don't think so

• ###### Re: Median without AGGR: Performance Issues

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

• ###### Re: Median without AGGR: Performance Issues

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

• ###### Re: Median without AGGR: Performance Issues

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?

• ###### Re: Median without AGGR: Performance Issues

It changes based on selections on the front end?

• ###### Re: Median without AGGR: Performance Issues

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

• ###### Re: Median without AGGR: Performance Issues

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

Left Join (Table)

Median(Temp) as Median

Group By Drug;

Patient,

Only([No of Days]) as Temp

Resident Table

Group By Drug, Patient;

Now when nothing is selected, everything looks good

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

Is that acceptable?

• ###### Re: Median without AGGR: Performance Issues

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

• ###### Re: Median without AGGR: Performance Issues

Hi Sunny,

Left Join (Table)

Patient,

Time,

Only([No of Days]) as Temp1

Resident Table

Group By Drug, Patient,Time;

If we group by time also , we can have the correct median.

Any thoughts on this?

• ###### Re: Median without AGGR: Performance Issues

Then without selection of time, you will have more than one medians.... not sure what the expectation with and without selection is