1 2 Previous Next 16 Replies Latest reply on Jun 8, 2016 1:28 AM by Philip Shemella

# How can I replace null records in a view with 0 for trend line?

I have a view which aggregates data on a daily basis. Then, I have a trend line which computes the moving average over the past 5 days (days in columns). The problem arrises, when a particular row in the data set (a user) is missing data for a particular day, the moving average function doesn't view that day as 0, and goes back to previous days where there is data and averages that data.

How can I treat these null records (days where there is no data) as 0 so that 0 is computed as part of the moving average?

Please help, I've searched far and wide for a solution, and I'd rather not modify my source SQL to insert a bunch of rows with 0.

• ###### 1. Re: How can I replace null records in a view with 0 for trend line?

To Tableau the missing row means that the measurement wasn't made, as opposed to the measurement that was made and returned 0.

Tableau v7 has built-in function to complete natural domains like dates, i.e. if you build a time series on data with missing days, you can tell Tableau to "show missing values" for missing dates.

As far as calculating moving averages, I don't think there is any other way apart from producing a row with 0 for each missing element in data to indicate that the missing data in this case means 0.

• ###### 2. Re: How can I replace null records in a view with 0 for trend line?

Hi Omar,

I have a rough idea of how to go about doing this, it would involve a custom moving average calc where you'd use the LOOKUP() function on date to determine if there were any missing days, figure out how many missing days there were with something like DATEDIFF('day',LOOKUP(ATTR([Date]),-1),ATTR([Date])), then use some more LOOKUP() and/or WINDOW_SUM() with the right settings to return the proper rows to create a total that would then be divided by 5.

However, this still runs into several issues.

If a user has no data for the end of the period, then Tableau will not perform the calculations and not draw any marks. What I mean is, if you are reporting users through Jan 31 and a given user A was active through Jan 30, the moving average for user A will stop at Jan 30 while everyone else continues to Jan 31. This will look strange.

If a user has no data for more than 5 days in a row, I imagine you'd want the trend line to drop down to 0 over those 5 days and then go up again. However, Tableau wouldn't be performing any calculations for those days (since there's no data) so the line would either break or connect over the intervening space, depending on whether your date dimension is discrete or continuous, and how the measure's special values for Marks are set up in the view. If the line breaks, then it's not showing 0, if the line connects, then it's inaccurate because it's not showing the trend to 0 and then up again.

In larger data sets, performance would be impacted due to the complexity of the necessary calc.

I think you'd be better served reworking your SQL to pad the data, the effort there will let you get exactly the results you want and not have any of the above issues.

Jonathan

• ###### 3. Re: How can I replace null records in a view with 0 for trend line?

Another manual solution I've used before is to create a calculated-field in lieu of a table-calc-on-your-measure that actually just takes the #-of-day sum and then hard divides by the number-of-days.

(so a sum divided by a count rather than an average function)

So if you're dead set on using a 5 day average, it'd be:

WINDOW_SUM(SUM(measure1),-4,0) / 5

// gives the sum of the measure over the last 5 days divided by 5

If you want to do something a little slicker, you can allow the user to change the #-of-days window by making a parameter (type integers/float, think the type will have to match your measure) to choose from:

e.g., 5, 7, 10

Then you'd use something like

WINDOW_SUM(SUM(measure1),-(parameter1-1),0) / parameter1

So when the user picks 7, your moving average re-calculates as a 7-day instead of a 5 day...

• ###### 4. Re: How can I replace null records in a view with 0 for trend line?

I have the same issue as Omar. My database already has a calculated field that is plotted over a 2 year time period with a trend line for each data series. The user must be able to filter for any combination of about a hundred different choices to include in the graph and trend lines. If there is a null value when making only one or two selections, the trend line is not correct and the graph connects data points that should not be. I have a very large data set and am not really keep to how to make a custom SQL work to import the data. I was hoping to use an IFNULL in the calculated field, but that has not yielded the proper results. Any ideas for a null calculation within a calculated field?

Calculated Field :

( (count([OTS M Only])*100) ) / max( [767_RevDept] )

I have tried...

IFNULL( (COUNT([OTS M Only]) * 100 , 0 )

IIF( ISNULL( (COUNT([OTS M Only]) * 100 ) , 0, (COUNT([OTS M Only]) * 100 )

• ###### 5. Re: How can I replace null records in a view with 0 for trend line?

Hi Frank,

Since this series of posts from several months ago I've learned more about how Tableau deals with null/missing data, and more importantly how to make use of some of Tableau's features. If you post a packaged workbook with some sample data, I can probably help you get the results you want. It's hard to give you an exact answer because based on your description I'm not sure whether there truly is no data, whether there's no data but Tableau is creating an empty cell, whether you've turned on date padding using "Show Missing Values", etc.

Jonathan

• ###### 6. Re: How can I replace null records in a view with 0 for trend line?

Jonathan,

Thank you for the response and I hope you had a wonderful Christmas.

Here is a packaged workbook with an example. The first chart has all the data included so every date shows up. The second graph is filtered for just one value which in turn will exclude some dates. When this happens, if there are no events in a certain month, the data will not be there resulting in a null value. I have tried to select 'Show Missing Values' but nothing happens. Every month of the year for the 3 years is in the data set so the graph works fine when enough selections are made to grab all the months for a two year period. My next option was to add a table to the data with every month in a 5 year time frame to cover every option and somehow link it to the rest of the data. I'm not very confident that will work so I'm still looking for other alternatives.

Thanks again for taking the time to help.

Frank

• ###### 7. Re: How can I replace null records in a view with 0 for trend line?

Hi Frank,

You're saying "Null", when really there is no data. If you look down in the bottom left, you can see 47 marks in the "Missing Null Values", whereas if there *was* data for 24 months & 4 colors we'd expect 96 marks. This is an important distinction to be aware of.

Here's what you can do to turn on Tableau's date padding and have the marks show up at 0:

1. Change the green MONTH(Date) pill on Rows to discrete.

2. Right-click on the pill again to turn on "Show Missing Values.

3. Right-click on the green Rate Calculation pill and choose Format...->Pane Tab->Special Values section->Marks combo box->Show at Default Value.

Now you're back to 96 marks.

Jonathan

• ###### 8. Re: How can I replace null records in a view with 0 for trend line?

Jonathan,

Thanks for the quick reply. I changed my workbook per your suggestions, and the data points do come out as expected.

However I'm now left with no connecting lines between certain points and a trend line that won't show up. I haven't found a way to have a trend line with discrete values and that's what I need for the analysis I'm trying to create. Is there a way to make those happen with discrete values or will I have to pad the data to get the result I'm looking for?

Frank

• ###### 9. Re: How can I replace null records in a view with 0 for trend line?

I'm not sure what you mean by "no connecting lines between certain points," can you give an example? The instructions I gave will draw a point for every combination of OTS M Only (group) and the Month of Date, so 23 months * 4 groups = 92 points for ATA = 2120.

However, Tableau won't pad data beyond the first/last or min/max of the data being padded, so if you change the filter there are only 66 points drawn, because that data doesn't include any Cancels or January 2011. If you really want full padding then you're going to need to at least help Tableau with the missing data, if not do the padding yourself.

As for trend lines, your options are to do the padding yourself so you can use a continuous (green pill) date axis, or build your own trend line calculations that could be used with the discrete date headers.

I'm sorry there's no easy, optimal solution here!

Jonathan

• ###### 10. Re: How can I replace null records in a view with 0 for trend line?

Hi Jonathan,

I have a State map and County map (USA) which shows data only when there is a value in the database. I have a requirement to show if any of the State or County does not have value still i wanted to show 0.00% that means for all null values i have to replace with zero in my State and County map. Please help me how to achieve this.

Regards,

Keerthana

• ###### 11. Re: How can I replace null records in a view with 0 for trend line?

You might try:

zn(lookup(sum([Measure]),0)) in place of the original measure.

• ###### 12. Re: How can I replace null records in a view with 0 for trend line?

Hi Matthew,

I tried it but no luck

• ###### 13. Re: How can I replace null records in a view with 0 for trend line?

Jonathan,

Thanks a lot for your insightful post. It helped me in solving an issue for a client. Appreciate your help.

Srini

• ###### 14. Re: How can I replace null records in a view with 0 for trend line?

Bravo Jonathan Drummey This was exactly what I was looking for, thanks.

1 2 Previous Next