Median without AGGR: Performance Issues

[#1]

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

[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

[#3]

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

[#5]

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?

[#7]

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?

[#9]

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

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?

[#11]

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