topic Re: Set analysis provide average based on one specific field selection in QlikView

Set analysis provide average based on one specific field selection

Tue, 21 Jan 2014 11:09:53 GMT

Hi,

I have a field called 'IllnessGroup'. One illness group may contain many patients all of whom may have had many events. I have a map with each of the patients plotted. What I am wanting to achieve is to have all the patients with an average waiting time (accross all the events they have) of less than the population average to be green else be red. To start with I used the following expression within the 'Background colour' of the chat expression on the map:

IF(AVG(TimeBetweenEvents)>[AVG(TimeBetweenEvents)],red(),green())

In the script I have calculated the field [AVG(TimeBetweenEvents)]. This roughly works as it does indeed colour the dots red or green depending on the average value of 'TimeBetweenEvents' for each patient. The problem is that some illness groups have much longer 'TimeBetweenEvents' values meaning that if you select that 'IllnessGroup' you will always see a red dot.

Instead what I really want is for the colour coding to be relative to the users selection on 'IllnessGroup'. For example if the user has the 'IllnessGroup' selection of Heart attack and stroke and the user has select just three specific PatientIDs I would like the map to display the colours for those three users as green or red based on if they are above or below the collective average 'TimeBetweenEvent' for the all Patients within the 'IllnessGroup' of Heart attck and Stroke.

Any pointers would be greatly apprecaited.

Thanks

Dan

Re: Set analysis provide average based on one specific field selection

Nicole-Smith — Tue, 21 Jan 2014 15:39:54 GMT

So population average is supposed to only be filtered on IllnessGroup and nothing else?

IF(AVG(TimeBetweenEvents)>AVG({1<IllnessGroup=P(IllnessGroup)>}TimeBetweenEvents),red(),green())

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 10:17:42 GMT

That doesn't seem to quite work. All the points are colored green with that formula. My only selection has been made on illnessgroup. Therefore you would expect to see some red and some green on this map.

Thanks for your help I really appreciate it.

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 10:25:36 GMT

Hi,

Try This

IF(AVG(TimeBetweenEvents)>AVG(Total <DimensionInMap>TimeBetweenEvents),red(),green()).

Replace DimensionInMap with your dimension used in map.

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 10:30:42 GMT

I have tried it but I still have the same issue when I use:

=IF(AVG(TimeBetweenEvents)>AVG(Total<PatientID>TimeBetweenEvents),red(),green())

I wasn't too sure if I should be removing either of the greater/less than signs from your formula around the ChartDimension or not.

Thanks

Dan

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 12:28:03 GMT

IF(AVG(TimeBetweenEvents)>[AVG(TimeBetweenEvents)],red(),green())

How do you calculate [AVG(TimeBetweenEvents)]?

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 12:32:09 GMT

This was being calculated in the script. I realise this is the wrong way to go given I want it to be a dynamic value based on the users illnessgroup selection, but I'm not sure how I would achieve that.

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 12:40:17 GMT

Ok, so I just want to understand your need:

you want to see if a certain patient/s on average, wait longer than the average wait time for a specific illness group?

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 12:48:48 GMT

Almost but slightly more...

As a user you select one or more illnessgroups. Based on that selection of illnessgroup (and disregarding the impact of any other field selection), I want the color to be red if the patients average waiting time was greater than the average waiting time for all the currently selected illness group.

For example if the user has selected illnessgroup of 'Heart Attack' and also selected [AGE] of 55+ I would want the chart to show all those patients that are over 55 colored red if they waited longer than the average waiting time else green. It would consider the entire population of Heart Attack when calculating the average to benchmark the patient against.

Similarlly if both Heart Attack and Cancer is selected I want to take the average waiting time of all patients for those two groups. This will if given a normal distribution (and no other selections have been made on other fields) there will be roughly half green and half red dots on the chart.

If for example you have selected Cancer and 55+ and that people that are over 55+ are usually fast tracked with below average waiting times you would expect to see the majority of the dots being green as we would still be using the whole population of cancer as the benchmark.

I hope those examples make it a bit clearer, apologies it isn't more elegantly put.

Thanks

Dan

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 13:01:57 GMT

Okay, last question.

what data sources are you using?

do you have 1 large table for all patients or you use different type of schema?

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 13:10:41 GMT

I have one large fact table containing all of the event data. The fact table contains the waiting times and the patient ID. See screenshot below:

Re: Set analysis provide average based on one specific field selection

Wed, 22 Jan 2014 13:19:59 GMT

I would propose to create a table that would include all dimensions you want the user to select upon when you

refer to the total average of time between events, the metric would be the timebetweenevents.

you can group this table by the illness group and then write the expression you want in the set analysis when on the left you write the average time between events of the fact table and on the right you out the average time between events of the created table.

it would make the selections of the subscriber dynamic and a certain patient/s selection won't effect the total average of the illness group.

Re: Set analysis provide average based on one specific field selection

Nicole-Smith — Wed, 22 Jan 2014 13:50:59 GMT

Hi Daniel,

Can you post a sample .qvw? This calculation should be easy enough to write, it's just hard to do without having the .qvw in front of me.

Re: Set analysis provide average based on one specific field selection

Fri, 24 Jan 2014 08:06:55 GMT

Please see attached the QVW file. Please note all data contained is test data there is no real data contained.

Re: Set analysis provide average based on one specific field selection

Fri, 24 Jan 2014 08:07:51 GMT

Thanks for your suggestion Brois, I don't fully understand exactly what you are suggesting. If possible could you try to put a bit of an example into the QVW that I have now provided?

Thanks for your time and help.

Re: Re: Set analysis provide average based on one specific field selection

Christian_Lauritzen — Fri, 24 Jan 2014 08:50:00 GMT

Daniel,

Attached is what I think you are looking for. It selects colours based on the average in the IllnessGroup.

Christian

Re: Re: Re: Set analysis provide average based on one specific field selection

Christian_Lauritzen — Fri, 24 Jan 2014 09:55:23 GMT

It struck me that you might wish to keep the colors relative to the IllnessGroup, even when selections in the IllnessGroup listbox is changed. In that case, a new solution is attached. I added IllnessGroup as a new dimension and added it as Total <IllnessGroup> after the total in the denominator to get subtotals per IllnessGroup. Now, a given point does not change color depending selections in the IllnessGroup listbox. In the previous upload, they are relative to groups selected if that is what you wish.

Best regards,

Christian

Re: Set analysis provide average based on one specific field selection

Fri, 24 Jan 2014 14:34:12 GMT

Thank you Christian, spot on.