Curious why this function doesn't work. The 0's in...
# gd-beginners
d
Curious why this function doesn't work. The 0's in this are NULL (which is probably the issue). Whats the workaround?
f
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.
d
The issue is the entire column is NULL 0 even if I get the 0 and 1 I'm looking for
đź‘€ 1
f
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?
m
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:
Copy code
``````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.
d
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
Copy code
``````SELECT IFNULL(SELECT
COUNT({label/local_user_id})
WHERE {label/case_opened.weekOfYear} = PREVIOUS(WEEKOFYEAR),0
)``````
Before Previous Week
Copy code
``````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
Copy code
``````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
Copy code
``````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``````
đź‘€ 1
m
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):
Copy code
``````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:
Copy code
``````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.
And one small note about the date filters - you seem to be using weekOfYear and monthOfYear and not month and week attributes*.* I am not sure if you are using these intentionally and correctly. They are both non-sequential date attributes that do not include the year in them. Value of â€śmonthOfYearâ€ť is not â€śJanuary 2024" but just â€śJanuaryâ€ť (of any year). So
``{...monthOfYear} = previous(MONTHOFYEAR)``
is not just
``April 2024``
but also
``April 2023``
,
``April 2022``
and any other April. The same case is with the weekOfYear vs Week. If you just want â€śnormalâ€ť sequential previous month and previous week, you might want to change â€˘ the
``{....monthOfYear} = previous(MONTHOFYEAR)``
to
``{...month} >= previous(MONTH)``
â€˘ and
``{...weekOfYear} = previous(WEEKOFYEAR)``
to
``{...week} < previous(WEEK)``
d
@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)
Copy code
``````SELECT count({label/user_id})
WHERE(
SELECT IFNULL(AVG({metric/workflow_completion_percent-_store_comms}),0)
BY {label/user_id}
) = 0``````
Copy code
``````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)``````
@Michal HauzĂ­rek thanks for other suggestion. I did notice that and had a worse solution to it. I'll update it to yours
m
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:
Copy code
``````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``````