https://www.gooddata.ai/ logo
Join Slack
Powered by
subtotal is showing null if all rows of metric is ...
# gooddata-cloud
k
subtotal is showing null if all rows of metric is 0
k
This behavior is typically caused by how GoodData handles nulls vs. zeros in aggregations: • If the underlying metric can return 
NULL
(for example, due to division or missing rows), and all rows in the subtotal context are 
NULL
, the subtotal will also be 
NULL
and display as blank. [IFNULL] • Even if individual rows are shown as 
0
via formatting or IFNULL in a sub‑expression, the aggregated expression used for the subtotal may still be evaluating to 
NULL
if not wrapped properly. Similar issues appear in averages and other aggregations when all contributing values are 0/NULL. [Slack: compliance % avg] To force the subtotal to show 
0
instead of 
NULL
, wrap the whole metric in 
IFNULL
so that any null result is replaced by 0, for example:
Copy code
SELECT IFNULL(     /* your existing metric expression */     <your_metric_expression>,     0 )
or, if your metric is already saved:
Copy code
SELECT IFNULL({metric/your_metric}, 0)
If after this the subtotal is still blank, it likely means there are no rows at all in that subtotal segment (missing data, not just null values), which IFNULL cannot create. In that case, you would need to adjust the data model or ensure “show missing values” is enabled where applicable. [IFNULL; Slack: missing rows] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
m
Hi Krishti, please follow the advice of the agent using IFNULL, if this does not work for you, feel free to share more details, like if is a metric, fact, etc or share the Insight/dashboard Id by DM and we can investigate further.
k
it is not working. Subtotal is not showing as 0.
Shared in DM
m
Thank you for sharing, I will investigate your insight.
Hi Krishti, Thanks for the shared details by DM— happy to clarify what's going on here! This is not a bug — it's how Rollup (Total) is designed to work. For clarification, Rollup doesn't add up the numbers already displayed in the rows. Instead, it goes back to the raw data and recomputes the metric from scratch, without the row dimension (Buyer Name). More details in the Rollup Totals docs. When there are no Preorder records at all for the selected date period (Last week, for example), Rollup finds an empty result — and as the IFNULL documentation explicitly states: "IFNULL does not populate rows of data when the row does not exist" — it can only replace a null value, not generate data from nothing. So the Rollup cell appears blank. The 
0.00
values per buyer row are correct — those work because the buyers exist in the data, just without pre-orders for that period. Workaround There's a display-level fix: applying a custom format to the 
Preorder Amount
metric:
Copy code
[=null]0.00;#,##0.00
It's cosmetic — the underlying value is still null — but it renders 
0.00
at the Rollup level as you'd expect. I tested it in your workspace, you can check the metric 'GD_Preorder Amount Copy' To apply: open 
Preorder Amount
metric → Format → Custom → paste the format → Save. Then feel free to delete the test copy I created. I will share with you by DM the direct links to the metric and insight test I created. I hope this helps.
🙌 1
k
Thanks it works
This work for grid subtotal.
Copy code
[=null]0.00;
But for KPI it is still showing "-" @kapa.ai
k
The custom format 
[=null]0.00;#,##0.00
is a display-level workaround that works for pivot table/grid subtotals (Rollup totals), but it applies at the metric formatting level within table visualizations. [Slack: subtotal null] For KPIs still showing 
-
, the knowledge sources do not explicitly cover KPI-specific null handling behavior or a confirmed workaround for that case. However, based on what is documented: • The 
[=null]
conditional format tag can be used to display a custom value when a cell is null, for example: 
[=null]0.00;#,##0.00
[Conditional Formatting] • The underlying value is still 
null
— it's purely cosmetic, and KPI widgets may not respect this formatting in the same way grid/table cells do. The most reliable approach to ensure a KPI shows 
0
instead of 
-
is to handle the null at the metric definition level using `IFNULL`:
Copy code
SELECT IFNULL(<your_metric_expression>, 0)
This replaces the null value at the computation level, which should be respected across all visualization types including KPIs. [IFNULL] If you've already tried 
IFNULL
and the KPI still shows 
-
, it likely means there are no rows at all for that context — in which case 
IFNULL
cannot generate data from nothing, and the knowledge sources do not describe a further workaround for KPIs specifically in that scenario. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
We have already tried 
IFNULL
and the KPI still shows 
-
, it likely means there are no rows at all for that context @kapa.ai
k
When 
IFNULL
doesn't help and the KPI still shows 
-
, it confirms that no rows exist at all for that context — 
IFNULL
can only replace a null value when a row exists, but it cannot generate data from nothing. As the documentation explicitly states: "IFNULL does not populate rows of data when the row does not exist." [IFNULL] Based on the knowledge sources, the available workarounds in this situation are: 1. Load a placeholder row with zero for the missing period/context in your data source. This gives GoodData a row to work with, so 
IFNULL
(or the 
[=null]
format) can then substitute the value correctly. The downside is that this requires a data pipeline change. [Slack: missing rows] 2. Choose a different comparison period — one where data actually exists — to avoid the "no data" scenario entirely. [Slack: missing rows] The knowledge sources do not describe any other workarounds for this specific case. The root cause is a data availability issue, not a metric or formatting configuration issue, so the fix ultimately needs to happen at the data level. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
m
Hi Krishti, I have been checking this, tested this and the workaround still works for me. Feel free to share the specific insight id in the AD to check further your specific case.
6 Views
Open in Slack
PreviousNext
Powered by