Apologies, I'm not able to create a suitable test dataset to simulate the blanks.I tried in Excel and in text file, but couldn't get them to be read in as blanks in Tableau.
Of what type is your true datasource? I've attached what I tried in the Forum Thread.
Trying to figure out what conditional you can use to detect the blank so that you could
try a calc like:
WINDOW_AVG(SUM(IF [LOS]<> //pseudocode for blank//
THEN [LOS] END))
Are you showing the average as a separate calculated field or in the Grand Total row?
I am attaching a workbook with all of the personal ID stripped away. The fields I am trying to calculate averages on are calculated fields that use a set. Maybe that is the issue??
2 of 2 people found this helpful
If I understand you correctly, the currently definition of [LOS DIscharged] is:
IF [Departed Patients] THEN [LOS] ELSE 0 END
which forces a value of 0 for those patient who are NOT Departed.
And this is intentional because those 0s are needed in the counting of
For the purposes of calculating the average I created a calc without the zeroes:
IF [Departed Patients] THEN [LOS] END
So the trick will be to use the no-zeroes calc for the grand total but the zerosversion for populating the table:
THEN AVG([LOS Discharged no Zeros])
ELSE SUM([LOS Discharged])
This method is described here:
This does have the unfortunate side effect of changing the color range
so that the max color is what is on the screen instead of the total dataset.
Please see workbook v10.5 attached in the Forum Thread.
Swaroop - thanks so much.
The answer was actually incredibly simple but your explanation was what led me to find it. The problem all along was with my calculated fields for admitted and departed patients - For example - IF [Departed Patients] THEN [LOS] ELSE 0 END. I don't need that zero there at all - not for any of my other calculations! I didn't realize those fields contained zeros because the fields are blank when I look at the data in tableau. I must have some setting turned on that does that?
At any rate - I changed the calculation to :IF [Departed Patients] THEN [LOS] END and did the same for my admitted patient field and it all works. I didn't even need to make a calculated field for the average - tableau does it for me by going to analysis>Totals>calculate all totals using>average and it all works perfectly.
I'm sorry you went through the trouble to figure out that calculation for me but it was your explanation that made me see exactly what was going on so thank you so much!
Glad you came to the resolution.
Yes, I hadn't noticed it before, but the default format for the
[LOS Discharged] field looks to be hiding the zeros.
Please see screenshot below.
The second column is with the default setting, that last column is without.