6 Replies Latest reply on Mar 31, 2020 8:02 AM by swaroop.gantela

    Missing Table Calc Data

    Mark Docherty

      Hello,

       

      I'm having two issues with the results from a Table Calculation not displaying correctly.

       

      Problem 1: When the Running Total does not change, I get a blank cell for my "Pace" Table Calc. See the entries for 3/22 as an example. Is there any way to get these to display a number?

      Problem 2: This is actually the bigger issue. For some reason, on 3/24 for some rows and all of them on 3/25, all of a sudden the Table Calculation result does not show up at all, even though the Running Total has changed (highlighted below). I have absolutely no idea why.

       

      I have attached a packaged workbook for reference. Any help I can get on this would certainly be appreciated.

       

      Thank you,

      Mark Docherty

        • 1. Re: Missing Table Calc Data
          swaroop.gantela

          Mark,

           

          I'm not sure, but I'm wondering if it might benefit from using a date scaffold:

          Creating a Date Scaffold in Tableau - The Flerlage Twins: Analytics, Data Visualization, and Tableau

           

          This may help fill in the gaps.

          • 2. Re: Missing Table Calc Data
            Mark Docherty

            Thanks for the suggestion but, so far, I have been unable to find a solution with scaffolding. The biggest issue is that scaffolding seems to work with a set range of dates, and I update this report on a daily basis. If there is a way to make date scaffolding automatically update to the current date, I have been unable to determine how.

             

            If there is a way, or there are any other thoughts or suggestions on how to resolve this issue, please let me know.

            • 3. Re: Missing Table Calc Data
              swaroop.gantela

              Mark,

               

              My apologies, I answered after only taking a quick look into your workbook.

               

              I'm wondering if it is a divide by zero situation that is causing the blanks.

               

              I don't know if this will correct your issues, but I tried using a lookup to

              get the previous day's denominator:

              (RUNNING_SUM(SUM([Total Margin] ) ) /

              ( IF ISNULL(AVG([Mar Work Days to Date (HWY)]))

              THEN LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1)

              ELSE AVG([Mar Work Days to Date (HWY)])

              END

              ) )

              *[Mar Working Days]

               

              It at least appeared to populate the table.

               

              Please see workbook v2020.1 attached in the Forum Thread:

              Missing Table Calc Data

               

              335316pace.png

              • 4. Re: Missing Table Calc Data
                swaroop.gantela

                Mark,

                 

                I don't think the scaffold will be needed in this case,

                but in general, I think it is ok to create a scaffold that by

                default goes far into the future, like 2030.

                 

                That is, unless you're dealing with a massive amount of data.

                 

                But in the end after the join to the scaffold, one can immediately

                filter out all those dates that are in the future.

                • 5. Re: Missing Table Calc Data
                  Mark Docherty

                  There are still a few missing items, but this did help for sure.  Thank you.

                  • 6. Re: Missing Table Calc Data
                    swaroop.gantela

                    Mark,

                     

                    Sorry for the stepwise approaches.

                     

                    Now this is uncanny, but I just received the exact same request at work,

                    to calculate off of missing values.

                     

                    This is the method I used for that problem, in this case I'm calculating the Avg Wk Days:

                     

                    IF NOT(ISNULL(AVG([Mar Work Days to Date (HWY)]))) THEN AVG([Mar Work Days to Date (HWY)])  // if there is a value, return it

                     

                    ELSEIF ISNULL(LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1))      // if the previous avg was blank, lookup what this function previously returned

                    THEN PREVIOUS_VALUE(0)

                     

                    ELSE LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1)    //  otherwise if there was a previous value, look it up

                     

                    END

                     

                    It seems to populate everything now, but I didn't verify the values.

                    Please see workbook attached in the Forum Thread.

                     

                    335316pace2.png