topic Re: Aggr in pivot adds a null for dim value in App Development

Aggr in pivot adds a null for dim value

dana — Sun, 14 May 2023 06:27:52 GMT

Hi People,

In order to resolve a total issue in average calculation, I use Aggr in my expression:

Sum(Aggr({<Service_Cost_Type = {'Service Parts'}>}
(Sum({<Service_Cost_Type = {'Service Parts'}>} Service_Part_Costs_Monthly)
/
Count({<Service_Cost_Type = {'Service Parts'}>} distinct Period))
,Cust,Part))

But this causes a null value for the Part dim.

It doesn't happen with the original expression:

Sum({<Service_Cost_Type = {'Service Parts'}>} Service_Part_Costs_Monthly)
/
Count({<Service_Cost_Type = {'Service Parts'}>} distinct Period)

I can resolve it using the AGGR expression only when SecondaryDimensionality =0,

but I would like to know what causes the missing dim.

Thanks in advance!

Re: Aggr in pivot adds a null for dim value

BrunPierre — Sun, 14 May 2023 09:02:43 GMT

Hi, I suspect the Part field may contain blank/null/whitespaces values.

Check null or blank
If(Len(Trim(Part )) = 0, 'IsNullBlank, 'NotNullBlank') as FieldCheck.

The simplest solution in a table might be to suppress zero values.

Re: Aggr in pivot adds a null for dim value

dana — Sun, 14 May 2023 10:04:17 GMT

Hi @BrunPierre ,

Indeed, there was one empty row in my data that caused it.

Thanks so much!