Curious why this function doesn't work. The 0's in this are NULL (which is probably the issue). Whats the workaround?

Hi Doug, it looks like this metric needs the IFNULL in order to properly display these null values; You can configure it to display any Nulls as zeroes instead.

The issue is the entire column is NULL 0 even if I get the 0 and 1 I'm looking for

I see what you mean! The syntax of the metric looks OK - may I suggest stripping it down a bit to try and understand where it fails? e.g.: remove the AND part of the WHERE filter and see if it works out. Also, how are these sub-metrics defined?

Hi Doug, are these ones and zeros in your metrics coming directly from your data tables (you have 1 or 0 or NULL there) or are those somehow calculated in GoodData with some logic in the metrics? If they are directly in your data (mapped to GoodData facts) , it might be better to use directly those facts for a filter like this, since that will be evaluated at the raw data row level without any aggregation, just like in SQL. Otherwise if there is some metric logic needed, this approach might help:

SELECT ...
WHERE IFNULL(metric1,0) = 1
AND ( IFNULL(metric2,0) = 0 OR IFNULL(metric3,0) = 0 )

If I am not mistaken, during the calculation, each (sub)metric is evaluated as if it was independent and the individual results are inner-joined to evaluate the outer metric. And without IFNULL if any of the three combined metrics does not have a value defined for a particular resulting row (is NULL), this row will fall out of the result. Which might be the case here, if you say the “existed before” are actually NULLs.

Each metric already has a IF NULL and the result is always 0 or 1. I have tried to double IFNULL now with the final, just left it an IF THEN for simplicity sakes and not differennce Previous Week

SELECT IFNULL(SELECT
  COUNT({label/local_user_id})
WHERE {label/case_opened.weekOfYear} = PREVIOUS(WEEKOFYEAR),0
)

Before Previous Week

SELECT IFNULL(
  SELECT COUNT({label/local_user_id})
WHERE {label/case_opened.weekOfYear} < previous(WEEKOFYEAR)
AND {label/case_opened.monthOfYear} >= previous(MONTHOFYEAR)
,0)

First Week Tried both This one just was NULL

SELECT COUNT({label/local_user_id})
WHERE IFNULL({metric/agents_online_-_previous_week} ,0) = 1
AND IFNULL({metric/agents_online_in_previous_time},0) = 0

This one just removed any 1,0 rows and made everything else 0

SELECT IF
 IFNULL({metric/agents_online_-_previous_week} ,0) = 1
AND IFNULL({metric/agents_online_in_previous_time},0) = 0
THEN 1 ELSE 0 END

Thanks for the details of the metrics. It seems that the IFNULL function is now also affected by the “show missing values” feature and without that, it is not returning data in these situations. We are already checking internally if this is intended behavior or not. Anyways it seems to work if you add FOR EACH … to your metric (only one level of IFNULL is needed then):

SELECT COUNT({label/local_user_id})
FOR EACH {label/local_user_id}
WHERE IFNULL({metric/agents_online_-_previous_week} ,0) > 0
AND IFNULL({metric/agents_online_in_previous_time},0) = 0

This should work if you are viewing your data by local_user_id. If you also needed it to work when viewed globally (without local_user_id in the bucket), this more robust metric with inner metrics should work:

SELECT COUNT({label/local_user_id})
FOR EACH {label/local_user_id}
WHERE (SELECT IFNULL({metric/agents_online_-_previous_week} ,0) BY {label/local_user_id}, ALL OTHER ) > 0
AND (SELECT IFNULL({metric/agents_online_in_previous_time},0) BY {label/local_user_id}, ALL OTHER) = 0

I hope this helps.

@Michal Hauzírek So did we get a feedback on this. I'm having a similar issue with another chart (maybe a simpler one). Where I want to count the number of users with NULL or 0. In the chart it does this ok. Howver, in the bar graph it shows 0 of the NULL (its mostly NULLs or Nones)

SELECT count({label/user_id})
WHERE(
SELECT IFNULL(AVG({metric/workflow_completion_percent-_store_comms}),0)
BY {label/user_id}
) = 0

SELECT IFNULL(SELECT{metric/workflow_completion_-_store_comms_2}/{metric/analyzed_-_store_comms_2}
WHERE {label/workflow_workflows} = "storecomms"
and {fact/workflow_clicked_workflows} = 1
,0)

Hi Doug, I do not have a final response from our team yet, but I believe in your other case it might be a very similar situation with the IFNULL not performing an outer join unless there is also FOR EACH. So I suppose adding

FOR EACH {label/local_user_id}

to the outer metric should help:

SELECT count({label/user_id})
FOR EACH {label/local_user_id}
WHERE(
SELECT IFNULL(AVG({metric/workflow_completion_percent-_store_comms}),0)
BY {label/user_id}
) = 0