Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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
You can try this?
=count({<COBDate ={'$(vReportingDate)'},XXX -= {"=NULL()"}>} DISTINCT XXX)
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.
Perhaps this?
=count({<COBDate ={'$(vReportingDate)'},XXX -= {"=Len(XXX) > 0"}>} DISTINCT XXX)
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)
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)
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.
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)
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
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())