5 Replies Latest reply on Sep 21, 2018 11:30 AM by tadd.wagner

# How to avoid "skipping" null rows in table calculation?

Hi ALL, it seems I have a question with the table calculation in tableau. The situation is described below:

Source data:

 Day Model Qty 1 A 1 1 B 1 1 C 1 2 B 3 3 A 1 3 B 1 3 C 1 4 C 3 5 A 1 5 B 1 5 C 1

Suppose we were producing three kinds of products: A, B & C. The data table shows the amount of each product every day. (Note: If some model is not produced some day, then the record is missing. E.g.: Day 2 has no records of model A/C, Day 4 has no records of A/B)

Now I want to draw a chart, showing the qty of each model and the total qty. The result should be like this:

But what I really got is this:

The total qty in some blocks is wrong. It seems during calculating the total qty with RUNNING_SUM (or WINDOW_SUM is the same), the NULL rows are skipped. For example, Model A(the blue block), in Day 1, the total qty is 3; In Day 2, the total qty should be 6, but the sum-up is skipped because no Model A is produced in Day 2; The same thing happens in Day 4.

Can anyone tell me how to make the table calculation DO NOT skip the NULL rows unless insert the missing records(mentioned above) to the source data?

• ###### 1. Re: How to avoid "skipping" null rows in table calculation?

Hi Cyu,

Is this what you are looking for? Please see the attached.

If so, all that you need to do is right click on day in your dimension list to the left, choose "create bins" then set "Size of Bins" equal to 1. Now use that in your columns instead of Day.

• ###### 2. Re: How to avoid "skipping" null rows in table calculation?

With data bins, we can show each model in each bar, no matter has record of that model or not. But the table calculation in counting total qty still skip the null rows.

The chart of now:

• ###### 3. Re: How to avoid "skipping" null rows in table calculation?

Cyu,

See the attached. Please look at calculated fields "test1" and "test2" and view those two under "edit table calculation"

1 of 1 people found this helpful
• ###### 4. Re: How to avoid "skipping" null rows in table calculation?

Hi Tadd, it works, really awesome.

Another question: If the "Day" dimension is not number, we cannot create bins on it, how to deal with it?

• ###### 5. Re: How to avoid "skipping" null rows in table calculation?

Cyu,

I'm glad that worked for you. Please mark the response as correct, so that the question will no longer show as "Not Answered."

For your additional question, please see this post: Binning Dates for Custom Date Levels | Tableau Software

If this does not lead you to your solution, I would suggest beginning a new discussion based on this question.