Need help in set analysis and null handling in expression

[ashmitp869]

Hi there,

I want to calculate the measures Uncoded Service Events show as 1 i.e  sum(SE) where [Coded Flag] is null.

here are my data :

I have used this query but the expression is not working.

Sum({$<[Coded Flag] ={'Uncoded'}> + <[Coded Flag]={"=isnull([Coded Flag])"}>}[SE])

[BrunPierre]

[Coded Flag]={"=Len(Trim([Coded Flag]))=0"}

[ashmitp869]

Hi there ,

the expression is still not working.

even though the 

[ashmitp869 · Accepted solution]

Actually , [Coded Flag] value is "Coded" and "Uncoded"

thus or part is not working- I need to check [Coded Flag] is null 

Sum({$<[Coded Flag] ={'Uncoded'}> + <[Coded Flag]={"=Len(Trim([Coded Flag]))=0"}>}[SE])

[BrunPierre]

Suppress null values on the dimensions.

[ashmitp869]

The coded Flag is not on the dim, but for testing I brought it.

Is main field is DRG Code - unable to suppress the DRG Code. As I need to show the DRG Code if null.

Is there any other way to handle it - with out suppressing the DRG Code.?

[BrunPierre]

To test if the filter actually works, disable all columns except SE.

[ashmitp869]

I tried to handle the null by load script -

[v_DIM_AR_DRG_TMP]:

seleclt

,CASE WHEN [DRG Code]= '960Z' OR [DRG Code] IS NULL OR len(trim( [DRG Code]))=0 THEN 1 ELSE 0
END AS CFlag

from table ;

[v_DIM_AR_DRG]:
LEFT KEEP([v_FACT_AP_SE_WAU])
LOAD
[DRG Code]

,if(CFlag=0,'Coded','Uncoded') as [Coded Flag]
, if(len(trim( [DRG Code]))=0,1,0) as Null_Flag

Resident v_DIM_AR_DRG_TMP;

but still can't handle it

[BrunPierre]

If row one is all you need, these two expressions will do.

Count({$<[Coded Flag] ={'Uncoded'}>}[# Rows])

Sum({$<[Coded Flag]={'Uncoded'}>}[SE])