Aggr Function

[tchovanec]

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.

[stevedark]

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

[tchovanec]

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.

[stevedark]

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

[tchovanec]

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?

[Gysbert_Wassenaar]

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))

[stevedark]

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

in the chart?