topic Re: Aggr Function in QlikView

Aggr Function

tchovanec — Tue, 24 Dec 2013 16:32:17 GMT

I am trying to aggregate some data in a bar chart and it is not working the way I would expect it. I have attached the example application. You will notice that the straight table has a value of 6.5 for the average and the stacked bar chart is showing something completely different. I would like the stacked bar chart to also equal 6.5. It should be the average of all acuity levels. Any help would be greatly appreciated. Thank you.

Re: Aggr Function

stevedark — Tue, 24 Dec 2013 16:50:47 GMT

Hi there,

If you add the Start Time to the AGGR parameters you will arrive with a value of 6.5:

AVG(AGGR(SUM(

DEPARTMENT_ID = {1000}

,StartDt = P(ISLAND_CAL.Date)

if(floor(DTTM_PAT_ARR,1/24/60) >= floor(StartDTTM,1/24/60) AND floor(DTTM_PAT_ARR,1/24/60) <= floor(EndDTTM,1/24/60),COUNTER_CSN)

),EndDTTM,StartTime))

This only gives a value for Acuity Level 4 though. I've not worked this back through to see why, but I suspect that there are issues in the data model. Simplifying things as much as you can, using techniques such as ApplyMap will help. Data islands always ring alarm bells, as they can cause things to go quite strange.

The way to simulate what you will get from an AGGR statement is to create a chart with the dimensions matching your AGGR parameters, put the SUM statement as the Expression on the chart and set the Total Mode to Avg of Rows. The dimensions in the table emulate what the parameters of the AGGR will do.

Stacking averages in a Bar Chart doesn't make too much sense anyway - as the total of the segments is never going to be the total average. You should perhaps stick to a single dimension on that chart?

Hope that helps,

Steve

Re: Aggr Function

tchovanec — Tue, 24 Dec 2013 17:02:53 GMT

Thanks for the response. I was able to get that same result, but as you said it only shows acuity 4. I am unable to figure out why that happens. I need it to stack all the acuities.

In regard to the island calendar being used, I had to use this because I have too many calculations that use different dates, such as admit date, arrival date, discharge date, etc.

I agree that stacking average may not make sense, but that is what the doctor wanted so I have to give them that. I appreciate the help.

Re: Aggr Function

stevedark — Tue, 24 Dec 2013 17:14:46 GMT

What is the 6.5 you are targeting based on? The values for each acuity are 27, 66, 60 and 53 - an average of 51.5.

Please explain further.

Cheers,
Steve

Re: Aggr Function

tchovanec — Tue, 24 Dec 2013 17:22:11 GMT

The 6.5 is the average number of arrivals for the days selected. The total arrived is 26 for the 4 days selected, so 26/4 = 6.5. They then would like to see that average 6.5 arrivals per day broken out by acuity and that is what I am trying to do. You will see that for acuity 2 there were 3 total, so an average of .75. Acuities 3 and 4 each had a total of 8, so the average would be 2. Does that help?

Re: Aggr Function

Gysbert_Wassenaar — Tue, 24 Dec 2013 18:16:14 GMT

Try

AVG(AGGR( count(

DEPARTMENT_ID = {1000}

,StartDt = P(ISLAND_CAL.Date)

if(floor(DTTM_PAT_ARR,1/24/60) >= floor(StartDTTM,1/24/60) AND floor(DTTM_PAT_ARR,1/24/60) <= floor(EndDTTM,1/24/60),DTTM_PAT_ARR)

),EndDTTM,ACUITY_LEVEL_C))

Re: Aggr Function

stevedark — Wed, 25 Dec 2013 08:15:39 GMT

That being the case, do you need to remove the date from the dimension list

in the chart?