topic Re: Median without AGGR: Performance Issues in App Development

Median without AGGR: Performance Issues

Fri, 23 Jun 2017 11:11:46 GMT

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

sunny_talwar — Fri, 23 Jun 2017 11:18:48 GMT

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

Fri, 23 Jun 2017 12:11:48 GMT

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

Re: Median without AGGR: Performance Issues

sunny_talwar — Fri, 23 Jun 2017 12:18:04 GMT

Do you have a sample you can share?

Re: Median without AGGR: Performance Issues

jonathandienst — Fri, 23 Jun 2017 12:23:56 GMT

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

Fri, 23 Jun 2017 12:37:33 GMT

Hi Jonathan,

No we cant use patient as a dimension.

We have only Drug as a dimension.

Re: Median without AGGR: Performance Issues

sunny_talwar — Fri, 23 Jun 2017 12:39:35 GMT

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

Fri, 30 Jun 2017 10:15:36 GMT

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

sunny_talwar — Fri, 30 Jun 2017 11:10:04 GMT

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

Fri, 30 Jun 2017 11:26:38 GMT

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

sunny_talwar — Fri, 30 Jun 2017 11:30:51 GMT

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

Fri, 30 Jun 2017 11:40:05 GMT

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

sunny_talwar — Fri, 30 Jun 2017 11:43:50 GMT

Don't think so

Re: Median without AGGR: Performance Issues

Fri, 30 Jun 2017 11:57:32 GMT

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

sunny_talwar — Fri, 30 Jun 2017 11:59:00 GMT

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

Fri, 30 Jun 2017 12:12:55 GMT

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

sunny_talwar — Fri, 30 Jun 2017 12:13:48 GMT

It changes based on selections on the front end?

Re: Median without AGGR: Performance Issues

Fri, 30 Jun 2017 12:22:57 GMT

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

Re: Median without AGGR: Performance Issues

sunny_talwar — Fri, 30 Jun 2017 13:02:56 GMT

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?

Re: Median without AGGR: Performance Issues

Mon, 03 Jul 2017 09:05:47 GMT

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