2 Replies Latest reply on Jun 26, 2018 9:39 AM by Beatrix Balogh

    Calculating 'Null' & Averages over Multiple Dimensions Using If Statements

    Beatrix Balogh

      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
      000134

       

      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!

        • 1. Re: Calculating 'Null' & Averages over Multiple Dimensions Using If Statements
          swaroop.gantela

          Beatrix,

           

          Please see if the workbook attached in the Forum thread is closer to the goal.

           

          I think most of the structure was there; I think what remained was fixing a null

          timeframe to the entire Partner.

           

          A Null Timeframe was demarcated by:

          IF ISNULL([Current Rate]) THEN 0 ELSE 1 END

           

          That was then used in a modified version of [Achievement Goals]:

          IF CONTAINS([Measure],'Measure 1')=TRUE

          THEN

              IF { FIXED [Partner],[Measure]:MIN([Null Timeframe])}=0 THEN "Not Reported"

                  ELSEIF [Total Numerator]=0 AND [Total Denominator] = 0 THEN "Achieving Goal"

                  ELSEIF [Overall Percentage] >= 0.52 THEN "Achieved Best Practice"

                  ELSEIF [Overall Percentage] >= 0.52 -(0.52*0.05) THEN "Within 5% of Goal"

                  ELSE "Below Goal"

              END

          END

           

          I think what was missing was fixing that [Null Timeframe] to the entire Partner.

          The rest of the calculation uses the Total versions of Num,Denom, and Overall %

          to make the strata, e.g.: [Total Numerator] = { FIXED [Partner],[Measure]:SUM([Numerator])}

           

          The Categories were then adjusted to use this new calculation, e.g,:

          COUNTD(IF [Achievement Goals Overall]="Achieved Best Practice" THEN [Partner] END)

           

          One additional important part was that the [Due Date Passed?] filter was added to the context

          (right click on pill, "Add to context) Improve View Performance with Context Filters

          1 of 1 people found this helpful
          • 2. Re: Calculating 'Null' & Averages over Multiple Dimensions Using If Statements
            Beatrix Balogh

            Hi Swaroop! Thank you very much - that worked perfectly!