How to Handle Single Value Indicator When No new records fit recent days

[Eric Jacobson]

I'm using this iif("Days Since Now"< 28, 1, 2) , to bucket data into column 1 or 2. Column 1 represent the last 28 days. The indicator works great, but only if there are records returned for both buckets. Is there a graceful way to handle the case where no records meet the last 28 days bucket? Current it shows an error on the dashboard.

This is a failures in last 28 days indicator. So it works great, but only when we have failures in last 28 days, ha!

[Skyler Ataide]

Hi @Eric Jacobson, From some testing with my own data, this does appear the be the current design of the Single Value indicator chart type - it requires numeric values in both columns to display. 

I will ask when there a no records returned for one of the buckets (columns), does the column for that bucket return NULL? If it is returning NULL, you should be able to apply a custom formula case statement to convert the NULL value to a zero. For example: 

CASE WHEN "COLUMN_1" IS NULL THEN 0 ELSE "COLUMN 1" END 

The Single Value Indicator chart type requires a numeric value in both columns, so changing any NULL values to zero should resolve the issue. 

[Eric Jacobson]

Unfortunately, no. I start with a record set of releases that required remediation. If there do not happen to be any releases that required remediation in the last 28 days, bucket one doesn't exist. Only bucket 2 exists (i.e., releases requiring remediation that are older than 28 days).

[Skyler Ataide]

@Eric Jacobson does the iif("Days Since Now"< 28, 1, 2) function that you are using generate two separate columns for the two separate buckets? If you are able to raise a support request our support team can take a closer look at the chart query you are using to generate the single value indicator chart and provide alternative suggestions on this custom query.  

[Eric Jacobson]

When it works (i.e., when I have records for both buckets), yes, two separate columns get generated. After a Transpose step, a column named "1" and a column named "2" are created. When no values exist for the "1" bucket, I end up with only a "2" column and the chart throws error.

....cool. I opened a support request. Thanks, @Skyler Ataide .