How to ignore space from set analysis

[ashis]

Hello,

I have a pivot table and I am using two calculated dimensions and few expressions.

In our data we have some white space , now the challenge is I need to ignore space and calculate my expression as well as dimensions.

For expression , I have used the following syntax:

=count({<v_Variable={'$(vReportingDate)'},XXX-={"=len(trim(XXX))=0"}>}  DISTINCT(XXX))

XXX has space as value in database.

Now in the calculated dimension I am using the following syntax :

=aggr(sum({<YYY=,v_Variable={'$(vReportingDate)'},XXX-={"=len(trim(XXX))=0"}>} ZZZ),BBB_CCC,DDD)

When I use the above expression , it is giving me "-" as value for all the rows.

I need to remove space data from my pivot table .

Could you please tell me how do I write it in set expression so that I would get the right result.

I tried the following expression to ignore space and null value, but no help.

=count({<COBDate ={'$(vReportingDate)'},XXX-={'=(NULL())'},XXX -={''}>} DISTINCT (XXX))

[ashis]

Hi Anil,

I am avoiding in script level , because I am not sure if this value is being used in any other place , I am not fully aware of the data.

Second thing if I put it in script level and replace space with null , then how do I handle it in the expression.

[ashis]

Hi Marcus,

I am little scare of doing it in the script level. I am asked not to filter data in the script level.

If I use the FieldFlag as you suggested, then how do I use it in the expression. For example,

In script:

if(len(trim(XXX))=0, 1, 2) as FieldFlag

In expression:

=count({<COBDate ={'$(vReportingDate)'}, FieldFlag-={1} >} DISTINCT XXX)

Is the expression will work for ignoring space. Please advise.

[marcus_sommer · Accepted solution]

Real NULL and also the mock NULL's like empty values or any kind of spaces could be quite troubling to detect / to consider within gui-expressions and it won't make it easier to develop any logics and expressions at all. Therefore the suggestion of transferring it into the script.

Your example of FieldFlag -={1} within your expression should work like expected.

- Marcus