Why Quick Sight default add table calculation (Percent difference) doesn't have any option to handle the NULL divide by zero errors?Its seriously lagging behind in terms of basic features compare to other BI tool available in market

Question & Answer
, ,
1

Why Quick Sight default add table calculation (Percent difference) doesn’t have any option to handle the NULL divide by zero errors?Its seriously lagging behind in terms of basic features compare to other BI tool available in market.
I have 20+ meassure values in a pivot table and I need to use WoW MoM and YoY calculation and show in a single column along with the weekly trend table typical Amazon WBR format. Surprisingly Quick Sight default add table calculation (Percent difference) doesn’t have any option to handle the NULL divide by zero errors which leave me only one option creating 60 calculated field using Lag formula and handle divide by zero/NULL error.
Any help appreciated.

2

Hello @Kousic , welcome the QuickSight community!

To my knowledge there is no way to tell QuickSight how to handle null values in table calculations. That being said, other users have found work arouns using ifelse and coalesce.

Here’s an example:

Let me know if this is what you were looking for. I will also mark this as a feature request for the QuickSight team.

3

I am using periodOverPeriodPercentDifference(distinct_count({cf_active_deals}), {ad_date}, WEEK, 1)
can you help me how to handle NULL/divide by zero error here if my previous period data is NULL or zero?

I was trying with LAG but it was not working either:
(
{video_preferred_deal} - lag({video_preferred_deal}, [run_date ASC], 1)
) /
ifelse(
lag({video_preferred_deal}, [run_date ASC], 1) = 0 OR isNull(lag({video_preferred_deal}, [run_date ASC], 1)),
1,
lag({video_preferred_deal}, [run_date ASC], 1)
) * 100

4

Hello @Kousic

When you say it wasn’t working do you mean it returned an error when you tried to save or the values it returned were wrong?

For your periodOverPeriodPercentDifference calculation you could try something like the following:
Check and replace null = ifelse(isNull({cf_active_deals}), 0, {cf_active_deals})
WoW= periodOverPeriodPercentDifference({Check and replace null}, {ad_date}, WEEK,1)

Basically what I’m trying to do is recreate the cf_active_deals (or other metric column) so that NULL is 0. Then do my calculating on that. If you add these to a table you’ll need to add the recreated field and then hide it to avoid a calc reference error.

You can also add a sample dataset to arena, that would be easier to troubleshoot: