Keeping null value in running total


(Jared Young) #1

Sometimes when graphing a cumulative timeseries curve there are future dates that get plotted. Right now the graph would show a flat line for forward looking dates, which looks odd and can confuse people reading the dashboard. Is there a way to delete those points on the chart? The one way I could think of would be to keep a null value when calculating the running total, but that does not seem to be an option. Happy to provide more specifics for my case if needed.


#2

Hey @beggarsblues!

To prevent the future dates from appearing on the visualization you will want to filter the data to exclude dates beyond the current date.

If simply filtering does not remove the future dates you will want to turn off dimension fill. To do so click the gear icon for the dimension and select ‘Remove Filled in Dates’ which will toggle dimension fill off.


(Jared Young) #3

Thanks Rachel,

Unfortunately that doesn’t appear to work. I believe the issue is because my y-axis are days of the week and I’m looking at cumulative totals week over week. If I’m midway through the week and the data isn’t populated then running_total will just see the nulls and add zero to the rest of the week. I don’t think there is a filter that can stop that from happening. And I’m also not filling in dates per se, so that didn’t work either.

Appreciate the response! I do think this is something that Looker should look into resolving. I can imagine this happens quite a bit as time series graphs often are not simply dates on the y-axis.


#4

Hey @beggarsblues,

Thanks for the details!

Depending on the type of chart you are using there may be a ‘Plot Null Values’ toggle you could switch to prevent any null values from appearing on the chart.

I’d be happy to assist you further in trying to get this worked out. If you could email support@looker.com with the specifics we will be able to dig into it further with you.

Thanks,
Rachel


(Martin Yim) #5

Hi @beggarsblues,

Not certain how your data is structured, but I was able to work around this issue in my own analogous situation by creating a calculated field in which you set the running total of field X equal to null if the X field is 0 or null, and then use this calculated field as the “running_total” while setting “plot null values” to false in the settings.

This way, your new calculated field running_total of field X now has null values where the running total would have flat-lined, and you set the visualization to not plot the nulls, hence stopping the visualization short before the flat-lining.

The psuedocode for the calculated field would be something like this:

if(is_null(X)=yes, null, running_total(X))