Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Keeping null value in running total

  • 28 August 2017
  • 6 replies
  • 1373 views
B

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.

6 replies

rachel.johnson
Rank
Userlevel 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.

B

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.

rachel.johnson
Rank
Userlevel 2

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 visit help.looker.com with the specifics we will be able to dig into it further with you.


Thanks,

Rachel

M

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))

Kyle_Webb
Userlevel 3

Hey I came across this trying to do the exact same thing. I figured out how to change the table calculation so that it doesn’t display any values in the future.


Something like this:

if(${order.created_at_day_of_month} > extract_days(now()),null,running_total(${order.count_created_current_period}))


Then make sure to deselect “plot null values” in the visual settings.


Before (flatlines at 17, which is today):

image


After (stops at 17):

image

GenDemo
Userlevel 4

I think I have a similar problem

I want to display the forward looking dates, but the line chart is plotting the null values, which confuse people. So I want an option to not plot these.

I used to do this in Excel using NA()

Reply


Terms of Service