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))

[Anil_Babu_Samineni]

You can try this?

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

[ashis]

Hi Anil,

The issue is not with null but space. We do not have null value in database. Though I have used your expression but the result is same.

[Anil_Babu_Samineni]

Perhaps this?

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

[its_anandrjs]

If you need to remove spaces then this to try

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

Or

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

Or

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

[ashis]

Hi Anand,

Thank you for your reply,

I have applied all the above logics that you have mentioned.

There is one problem with all these logics mentioned above. The problem is there is one calculated dimension, it has the following expression and it is getting disappeared , I am wondering why it is getting disappeared.

Any idea. Rest value is appear correct.

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

[ashis]

Thank you Anil for your response.

there is one calculated dimension that is disappearing , it has the following syntax.

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

or

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

Not sure why this dimension is not showing in my pivot table.

[its_anandrjs]

One by one ad different fields for by pass the selection on the SET expression, any field may associated on that field due this your values get disappear or may be condition not matching.

Ex:- Datefield=, Field2=, field3=

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

[marcus_sommer]

I think I would resolve the space-topic on script-level either by removing/filling these values with something:

if(len(trim(Field))=0, null() /* 'EMPTY' */) as Field

or by flagging the content maybe with this approach:

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

To get your calculated dimension to work make sure that your used dimensions are the correct ones.

- Marcus

[Anil_Babu_Samineni]

First one won't give anything due to syntax error.

Second, Probably give some thing but not sure, What you've done.

Can you try using Flag in script and let me know

If(Len(Trim(XXX))=0, Null())