Apply conditional format to line charts (time series) where value is null

Jarno Pons shared this question 2 years ago
Answered

Hi all,

The chart in the attached screenshots is a time series chart for the trend of internet traffic for the last 12 hours. Between 6 AM and 8.30 AM there are no datapoints available (meaning that the internet was down). I would like to set up the chart such that:

  • Conditional format is applied to the time period where we did not receice any datapoints (thicker line, or a large rectangle area in the chart)
  • The line shows null values as 0 between 6 AM and 8.30 PM, instead of extending the line from the last known datapoint to the next known datapoint.

Any ideas on how to fix this in Yellowfin?

Comments (8)

photo
1

Hello Jarno,

Thanks for reaching out with your question. I think the best solution to converting your null values to 0 will be a calculated field in the form of a CASE statement. Something along the lines of:

CASE WHEN FTTX IS NULL THEN 0 ELSE FTTX END

This will set any null values in the FTTX records to 0.

On the note of conditional formatting, you can add a Conditional Formatting rule to your metric as follows:

66a132424e07ba0fc0127f069091d22c

This would format the cell red whenever a 0 is present. Carrying this over to the 'Chart' step, click the arrow next to your field containing the conditional formatting and choose 'Settings'. Under the 'Conditional' menu you have a few options:

7273f7f0c4c82acd8aacbad8129a75b9

In this case, I've set the 'Style' to 'Area', which will give you a block of color over the range that applies to Conditional Formatting alerts.

Give this a try and let me know how it goes.

Thanks,

Ryan

photo
1

Hi Jarno,

I wanted to touch base on this and see if you've had a chance to review my reply.

Thanks,

Ryan

photo
1

Hi Ryan,

Thanks for your input! I forgot to reply on your suggestion, I am sorry.

Unfortunately your solution does not work in our case, because the dataset does not contain datapoints for specific points in time at all. Formulating a "CASE WHEN" statement only works when the dataset does contain datapoints (with NULL values).

For example, some datapoints (date) in our dataset are shown below. Notice the absence of 8.15 and 8.20 datapoints.

31/10/2017 08:00 31/10/2017 08:05 31/10/2017 08:10 31/10/2017 08:25 31/10/2017 08:30

photo
1

Hi Jarno,

Thanks for the reply. We do have a recently implemented Enhancement that may help. You can see below the 'Null Behavior' option, which allows you to define how your line reacts when there's a lack of data points:

3defd576330459e5345af411c511cd6c


You can try setting this to Zero and see if that helps.

The alternative work around would be to join your data via subquery to a table containing all the dates of a year, which would allow you to pull in nulls or zeros for missing dates in your data set.

Does this help?

Thanks,

Ryan

photo
1

Hi Jarno,

I wanted to reach out and see if you've had a chance to review my reply.

Thanks,

Ryan

photo
1

Hi Ryan,

Thanks for your help!

The "Null Behaviour" setting does not generate the desired result in this situation. The enhancement works in case there exist datapoints, but with empty columns.

Only alternative indeed is joining the data to a table that contains all timestamps and thereby creating datapoints with empty columns. Then we could properly use this "Null Behaviour" setting as well.

Thanks again!

photo
1

Hi Jarno,

Happy to help! I'll mark this as Answered. Don't hesitate to reach out with further questions or issues.

Thanks,

Ryan

photo
1

Hi,


I do not see Null Behavior on YF 8.0.01 build for line chart, is there any configuration to enable.


Regards,

Bharath

photo
1

Hi Bharath,


I can confirm that it does exist in 8.0.1 and above, but please note that this is only available for Time Series data (dates, timestamps). If you would like it to be available for other data types, I can raise an enhancement request on your behalf to be evaluated by our developers for future releases.


Cheers,

Neal

photo