topic Re: Replace null values of a measure with a text (Pivot table) in App Development

Replace null values of a measure with a text (Pivot table)

NG30 — Tue, 25 Apr 2023 12:27:48 GMT

Hi All,

I need to create a pivot table with a revenue sum measure, but whenever the revenue sum is null, it shows a string eg "null" instead of just the null, smth like this

	Product 1	Product 2	Product 3
Customer X	100	null	200
Customer Y	null	50	150
Customer Z	120	100	null

Is it any possible to do it through just the visualization?

I tried a bunch of different formulas but in all cases it returns null for null value cells:

if(coalesce(Sum( sales_revenue_amount_eur),0)=0,'Null','not null')
if( IsNull(Sum( sales_revenue_amount_eur)),'Null','not null')
if(Sum(sales_revenue_amount_eur)>0,1,-1)
if (Sum(sales_revenue_amount_eur)+0.1=0.1,'null',0)

if (coalesce(Sum( sales_revenue_amount_eur),0)+0.1=0.1,'null,Sum(sales_revenue_amount_eur))

if (isnull(Sum( sales_revenue_amount_eur)),1,0)
Sum( sales_revenue_amount_eur)

Thanks for help.

Re: Replace null values of a measure with a text (Pivot table)

Qrishna — Tue, 25 Apr 2023 13:43:42 GMT

Hi -

you may want to change here for what tto dispaly if the value is null.

Hope that helps.

Re: Replace null values of a measure with a text (Pivot table)

Sabrina_V — Tue, 25 Apr 2023 13:44:03 GMT

Maybe it doesn't work because you do not have this data in your model. This would be missing values and not null values.

In order to correct that you will need to add all the missing combinaison of values in the model

Re: Replace null values of a measure with a text (Pivot table)

NG30 — Wed, 26 Apr 2023 08:12:12 GMT

Thanks a lot @Sabrina_V !

Yeah, that's my problem, will fix in the model then.

Re: Replace null values of a measure with a text (Pivot table)

NG30 — Wed, 26 Apr 2023 08:14:45 GMT

Thanks @Qrishna ! That's helpful to know!