Hi Tableau Users!
I had a question around calculating nulls & averages over timeframes/dimensions using if-else statements.
In my attached workbook I am trying to get overall counts of how each of my 'Partners' is doing for a given 'Measure'. In this example there are 4 'Partners' that track 'Measure 1' for a given number of 'Timeframes'. I have it filtered so that it only shows those 'Timeframes' for which the 'Due Date' has passed - because those are the only relevant ones at the moment (this changes over time).
I have a sheet that outlines the 'Current Rate' for each 'Timeframe' for each 'Partner'. Then I have another sheet that has all the overall counts of who is meeting the highest level of the measure baseline, being within 5% of meeting the measure baseline, being below the measure baseline, and not reporting at all. The way I am calculating this is that it needs to take the average of all 'Current Rates' over the current timeframes and determine how they are doing based on that, however it seems like I'm not doing it correctly in terms of incorporating whether someone didn't report in one 'Timeframe'. E.g. in my overall counts sheet, I would want the first 5 columns (achievement counts) to add up to the total count of partners (6th column). So if someone didn't report in one timeframe - they only fall into the not-reported column/calculation.
It seems like my 'Not Reporting' portion works fine, but it looks like for the other achievement calculation portions it doesn't account for the fact that some of them have a null in one timeframe (e.g. it seems to ignore the null and just take the average of the values that are entered). Is there a way to make it so that it doesn't ignore the null and thus doesn't calculate a rate? I thought for some reason that the if-else statement and having the Null calc as the first line would always end the statement if things were Null, but I've definitely done something wrong.
E.g. in this example the correct overall counts should be (instead of what I currently have):
|# Achieved Best Practice||# Achieving Goal||# Within 5% of Goal||# Below Goal||# Not Reported||# of Partners|
I hope that I made sense - happy to try and explain in a different way if it isn't clear. And thank you in advance!
Test Calculated Field Nulls.twbx 32.8 KB