1 2 Previous Next 17 Replies Latest reply on Jun 15, 2016 3:34 PM by Joe Oppelt

    Include Null values as ZERO when calculating windows average

    Philippe Surget

      HI,

      I would like to take into account the Null values as a zero when doing an windows_avg calculation, I have the solution by including ZN within the formula (see snapshot below) and result month after month are correct (in green) .

       

      but in my dashboard I manage the visualization of results in the text marks following a field 'KPI Detailled', When I add the condition in the formula, calculation are still correctly calculated but results are not  displayed in months where there are the Null values (see below in red)

       

      Is there someone has an idea to cover that need?

       

      Thanks

      Philippe

        • 1. Re: Include Null values as ZERO when calculating windows average
          Joe Oppelt

          Are you getting NULL there because ATTR([KPI..]) is not equal to 'Overdue Cases Count' ??

           

          Also, what would happen if you enclosed the sum of the two WINDOW_AVG() in ZN ??

           

          Then

          ZN( Window_avg( ...

          +

          Window_avg( ...)  )

          end

          • 2. Re: Include Null values as ZERO when calculating windows average
            Philippe Surget

            Hi,

             

            To answer to your questions

            Q1: Are you getting NULL there because ATTR([KPI..]) is not equal to 'Overdue Cases Count' ?

            • No there are some months with no cases, so the sub-measures of the 2 fields ‘OCC – xxxxxx’ in the windows_avg are Null value. These 2 ‘OCC_xxxx’  field are a COUNTD(Cases) with some conditions. So the result can be >0 ; =0 or Null if there is no cases in a dedicated month.

            Q2: Also, what would happen if you enclosed the sum of the two WINDOW_AVG() in ZN

            • I have the same behavior, displaying results when the IF is removed but displaying blank when adding the condition.
            • 3. Re: Include Null values as ZERO when calculating windows average
              Joe Oppelt

              What happens when you try this:

              IF ATTR( ...   THEN

              Then

              Window_avg( ...

              +

              Window_avg( ...)

               

              Else 1

               

              end

               

               

              If you get a result of 1, then you are getting to the ELSE logic.


              Based on that, we'll think about what to do next.

              • 4. Re: Include Null values as ZERO when calculating windows average
                Philippe Surget

                Hello Joe,

                 

                Here what I get applying your proposal :

                • 5. Re: Include Null values as ZERO when calculating windows average
                  Joe Oppelt

                  OK.  That goes back to my original question:  Are you getting NULL there because ATTR([KPI..]) is not equal to 'Overdue Cases Count' ??

                   

                  The logic for ATTR(KPI...)   is dropping down to the ELSE section.

                   

                  What are you trying to do with that piece of the logic?

                   

                  What's happening there is that either ATTR(KPI...) is not equal to 'Overdue...', or there are multiple values of ATTR(KPI...) when Tableau is evaluating it (thus the value is actually ' * ' at the time).

                   

                  Can you post a sample workbook?

                  • 6. Re: Include Null values as ZERO when calculating windows average
                    Philippe Surget

                    Hi,

                     

                    I attached a workbook with details of calculation.

                    Field 'OCC - 6M (Avg) (KPI D)' give blank on Feb & Apr and wrong result for Apr & May.

                    Field 'OCC - 6M (Avg) (KPI D) (blk)' give the correct resulta and display all values.

                     

                    The logic used with the field 'KPI Detailed]' is to show in the same line several different measure for different KPI. In this worbook I kept only 2 KPI

                    Look at the first line, If you switch from one to the other you can see that I display either a count with the selection of Confirmed Outages (in red in below snapshot) or the windows_avg calculation by selecting the 'Overdue Case Count' (in blue).

                     

                     

                     

                    Hope that is help to understand my needs

                    • 7. Re: Include Null values as ZERO when calculating windows average
                      Joe Oppelt

                      See attached.  I have added the field [KPI] to the tooltips.  ( [KPI] is where [KPI Detailed] comes from.)

                       

                      For those two cells, [KPI] is null.  There isn't even a record in the data source for it.


                      When data is flat-out absent, the ZN function doesn't do anything for you.  ZN tells Tableau to use zero in place of a null value, but that's assuming a record is even there to have a null value in it.  In this case, with no record, the value of [KPI Detailed] isn't just null.  It's nothing.

                       

                      The ATTR function on that cell in the sheet is saying to do the calc if the value for the field in that cell is a particular value.  Since the field doesn't exist at all for the cell, it's not doing the calc.  (But when we put ELSE 1 in the calc, it was loading the value of 1 in the cell because the ATTR of the field in that cell didn't exist at all, thus it's not equal to 'Overdue...' .)

                       

                      Scroll tooltips in the attached sheet to see what I mean.

                      • 8. Re: Include Null values as ZERO when calculating windows average
                        Philippe Surget

                        Hi Joe,

                        I understood the issue. Now I need to find a solution to manage on a unique sheet the visualization of result from several KPI.

                         

                        Thanks for your help

                        • 9. Re: Include Null values as ZERO when calculating windows average
                          Joe Oppelt

                          Create a copy of the data source.  Blend so you get a direct match of your key dimensions.  (In the attached I blended on KPI Type and Technology Cluster.)

                           

                          The table calc we are messing with is [OCC - 6M (Avg) (KPI D)].  I made a copy of that.  (See [OCC - 6M (Avg) (KPI D) (copy 3)]. )  What I did in there is just detect when there are no rows in the secondary source for that cell.  In this case I just looked for the KPI Detailed field in the secondary being null.  That means it's just not there.  You could also do a COUNT([T_F_IR_MONTH+ (CMART_TABLEAU) (copy)].[KPI Detailed]) (or any other field) and if the count = 0 , the no rows in there.

                           

                          Now you can tell what's not there at all.  You can add this technique right into the original calc and handle the absence of data this way.

                           

                          Special note:  Each time you do stuff with this setup Tableau will tell you that you are blending the same data sources.  Blah, blah, blah.  Sometimes you might have to do this (as we see here.)  Just click the "Don't show again" box to tell Tableau to shut up about that.

                          • 10. Re: Include Null values as ZERO when calculating windows average
                            Philippe Surget

                            HI Joe,

                             

                            Can you elaborate more when you said 'Now you can tell what's not there at all.  You can add this technique right into the original calc and handle the absence of data this way.' not sure I have understood on how implement the technique in the current field and to have the corretc result display.

                            Sorry you have reach the limit of my technical competencies...

                             

                            Philippe

                            • 11. Re: Include Null values as ZERO when calculating windows average
                              Joe Oppelt

                              First of all, look at new calc  [OCC - 6M (Avg) (KPI D) (copy 3)].  I added it to the OCC sheet, displaying it as the second text item.  It puts out a value of 1.  (I used 1 just to put something there.)  It only does that when [KPI Detailed] from the copy of the data source is null -- meaning there is no data for that mix of KPI Type and Technology Cluster and month.


                              So you could modify your existing [OCC - 6M (Avg) (KPI D)] calc from its current logic, which looks like this:

                               

                              IF ATTR([KPI Detailed])= 'Overdue Cases Count'

                              THEN

                              WINDOW_AVG([OCC - Delivered Overdue (KPI D)],-5,0)

                              +

                              WINDOW_AVG([OCC - Open Overdue (KPI D)],-5,0)

                              END

                               

                              ... to look something like this:

                               

                              IF isnull(ATTR([T_F_IR_MONTH+ (CMART_TABLEAU) (copy)].[KPI Detailed]))

                               

                              THEN 0

                               

                              else

                               

                              IF ATTR([KPI Detailed])= 'Overdue Cases Count'

                              THEN

                              WINDOW_AVG([OCC - Delivered Overdue (KPI D)],-5,0)

                              +

                              WINDOW_AVG([OCC - Open Overdue (KPI D)],-5,0)

                              END

                               

                              END

                               

                              A note of caution:  Earlier you mentioned doing this for several KPIs.  Right now this calc is specific to 'Overdue Cases Count', and your current sheet is filtered for 'Overdue Cases Count'.  If you want to generalize this, you'll have to make sure this works for the circumstances you intend.  For instance you'll probably also want to blend on [KPI Detailed] as well as the other fields I used earlier so that Detail1 from the primary source matches up with Detail1 from the copy, and Detail2 matches up with Detail2, etc.  This is going to be specific to your needs in the final viz you're aiming for.  And that's starting to get into consulting services if you can't work that out yourself.  If you need consulting help, I can point you to some super contacts who participate on this forum. 

                              1 of 1 people found this helpful
                              • 12. Re: Include Null values as ZERO when calculating windows average
                                Philippe Surget

                                Hi,

                                I have tested your proposal but I think there is a misunderstanding.Indeed for I try to display is :

                                with the correct calculation of the average for the 2 months with no data Feb' and Mar' but as well for Apr' and May. Except if I made something wrong the result of your proposal produce the following results, 0 for the month with missing data but a incorrect average result for Apr' and May'.

                                Are you agree with my conclusion?

                                • 13. Re: Include Null values as ZERO when calculating windows average
                                  Joe Oppelt

                                  I'm not clear on what you are doing.  The first screen shot looks like the data from [OCC - 6M (Avg) (KPI D) (blk)], and the second screen shot looks like data from [OCC - 6M (Avg) (KPI D)].  So yes, you are going to see different results for those two sets of data.  It looks like you successfully inserted zero values in the missing cells for [OCC - 6M (Avg) (KPI D)].  Isn't that what we were aiming for?

                                  • 14. Re: Include Null values as ZERO when calculating windows average
                                    Philippe Surget

                                    Hi Joe,

                                    Unfortunatly I confirm that my intend is not to display zero instead Null but I want to see the real average result like [OCC - 6M (Avg) (KPI D)] is providing but by keeping the if condition with the field KPI Detailed.

                                    The kpi I'm working on, are based on tickets/cases opened by customers. So if for a given month there is no ticket open or if all tickets are excluded the number should be 0 in both cases. So the 0 should be taken into account in the average calculation like it's the done with OCC - 6M (Avg) (KPI D) like below.

                                    Is this clarify what I'm trying to do?

                                    1 2 Previous Next