10 Replies Latest reply on Oct 17, 2018 6:28 PM by Deepak Rai

# Calculate average of list in a crosstab

Hello all,

I am trying to calculate the average of a column of numbers in a crosstab I have made.  Here is a screenshot of my crosstab:

I am able to total up each column and have placed the column total on top of each column; however I cannot figure out how to find the average (I would also like to find the medi

I thought the calculation:

WINDOW_AVG(SUM[admitted LOS])

would work but it returns the value 59 all the way down the column, which is clearly not correct.  I can see that this calculation is calculating moving across the table and I need it to mve down the column but I cannot seem to figure out how to do that.

Thanks for any help - as always,

Lisa

• ###### 1. Re: Calculate average of list in a crosstab

WINDOW_SUM(SUM(LOS Admitted))/WINDOW_SUM(IF NOT ISNULL(SUM(LOS Admitted)) THEN 1 Else 0 End)

• ###### 2. Re: Calculate average of list in a crosstab

I still get the column of all 59's. I wonder where that number is coming from??

• ###### 3. Re: Calculate average of list in a crosstab

WINDOW_SUM(SUM(LOS Admitted))/WINDOW_COUNT(IF NOT ISNULL(SUM(LOS Admitted)) THEN Account Number End)

• ###### 4. Re: Calculate average of list in a crosstab

That equation wasn't valid but I tried this:  It didn't return any values at all

So then I changed the SUM to COUNT before the Account Number field and the 59's are back:

• ###### 5. Re: Calculate average of list in a crosstab

Divide by windowcount( sum(los)

• ###### 6. Re: Calculate average of list in a crosstab

That gives me all the 59's again.  So strange - you wouldn't think this would be so difficult- it's just calculating an average!!

• ###### 7. Re: Calculate average of list in a crosstab

Do you think it needs a LOD calculation ??

• ###### 8. Re: Calculate average of list in a crosstab

I need workbook

• ###### 9. Re: Calculate average of list in a crosstab

I wish I could send it - it's healthcare info and I cant share it. I will try to make up some dummy data tonight and send it tomorrow. So frustrating for something that seems like it should be so easy!

• ###### 10. Re: Calculate average of list in a crosstab

Lod will definitely work