Having an issue Filtering I have a table NAME METRIC 1 METRI GoodData #gooddata-cloud

Having an issue Filtering I have a table NAME, M...

Doug Creighton

02/18/2025, 3:05 PM

Having an issue Filtering I have a table NAME, METRIC 1, METRIC 2, METRIC 3. I want to filter out the names, where METRIC 1 is 0 OR NULL. However whenever I do filter by Metric 1 it does not do anything. The 0's are still there I tried using IFNULL and not IFNULL no this metric same outcome https://reporting.laivly.cloud/analyze/#/tenant-reporting-base-v3/de7b1c4e-abae-4dfd-8d68-d97f38a24760/edit

Mauricio Cabezas

02/18/2025, 5:30 PM

Hi Doug, thank you for contacting us. I have impersonate your organization as Admin, and checked the metric. For testing purposes I created the metric testGD

Copy code

SELECT 
CASE WHEN ({metric/utilized_-_spark_case_notes}) >= 0.85
THEN 0.0000000000000001 ,
WHEN ({metric/utilized_-_spark_case_notes}) < 0.85 THEN 
({metric/utilized_-_spark_case_notes}) 
ELSE 0 END

If you note the IFNULL seems is not needed, and I deleted the

AND

in the second condition. Please, can you confirm if is working as expected?

Doug Creighton

02/18/2025, 5:32 PM

I do need the AND in the second one though.

Mauricio Cabezas

02/18/2025, 5:50 PM

Ok, I see, you need to exclude the absolute zero in the second condition, to use them in the

ELSE

, like in the 6th row. Note if you keep the

AND > 0

, you still will see zeros. I just took away the IFNULL, it is what you expect?

Copy code

SELECT 
CASE WHEN ({metric/utilized_-_spark_case_notes}) >= 0.85
THEN 0.0000000000000001 ,
WHEN ({metric/utilized_-_spark_case_notes}) < 0.85 
and ({metric/utilized_-_spark_case_notes}) > 0 THEN 
({metric/utilized_-_spark_case_notes}) 
ELSE 0 END

Doug Creighton

02/18/2025, 7:23 PM

No, I don't want the names where there is NULL or 0. You can see rows have nulls still. The outcome I want is Names of rows that have a value in On Target. That was my original complaint - is it clear now what I'm looking for?

✅ 1

Doug Creighton

02/18/2025, 7:26 PM

The goal is to get rid of all of these blank names - but I can't do it. It will not filter when using a filter. Its very annoying - not sure what it is trying to do instead

Doug Creighton

02/18/2025, 7:28 PM

Below Target above 0% and clicked IF NULL then treat as 0 and yet it keeps them

Mauricio Cabezas

02/18/2025, 8:51 PM

Hi Doug, thank you for clarification. I will continue investigating and I will keep you posted with update.

✅ 1

Mauricio Cabezas

02/20/2025, 8:28 PM

Hi Doug, Thank you for your patience. The reason the filter "> 0" appears to be "not working" is that these values fall under the first condition according to the CASE statement in the metric, specifically "0.0000000000000001" (which is still greater than 0). However, they are formatted as "Percent Rounded," which leads to a display of "0." You can adjust the result expression in your metric to "0.1," and the condition will still correspond to ">= 0.85." After conducting various tests, we observed that when we click on "Show missing values" on the

Supervisor Name

attribute, it behaves as expected (I can see that this option is now selected). Can I confirm that this is the expected behaviour for you? In any case, we are consulting internally to our engineers to clarify whether "showing missing values" is effectively hiding the missing (blank) values, as this seems ambiguous. I will keep you updated once we receive more information from them. Once more, thank you for your patience.

Doug Creighton

02/20/2025, 8:29 PM

We did get it to work this morning with 0.01

✅ 1

🙌 2

Mauricio Cabezas

02/20/2025, 8:31 PM

I am glad it is working. I will continue to keep you updated about the "show missing values" for any future reference. Please let us know if you need any further assistance in this regard.

3 Views

Open in Slack

Previous Next