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

# Missing Table Calc Data

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

Mark,

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

This may help fill in the gaps.

• ###### 2. Re: Missing Table Calc Data

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

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.

Missing Table Calc Data

• ###### 4. Re: Missing Table Calc Data

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

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

• ###### 6. Re: Missing Table Calc Data

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.