Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to limit the dimension of the date dimension for my table.
So the first condition is: Show me only the Dates with a delivery Status in the B or M category, but the value of it cannot be 0:
=if( [Status] = 'B'or [Status]='M' and ([Deliveryvalue]<> 0, Dateofdelivery.autoCalendar.Date)
This works pretty fine. But I found out there are also Delivery positions which indeed can be of zero value. These [Deliverypositions] are always only the size of 1 and are of ZERO value. Something like a dummy delivery. I cannot combine these two so easily since there are also Deliveryposition in the size of '1' but can worth much.
I tried something like this:
=if( [Status] = 'B'or [Status]='M' and ([Deliveryvalue]<> 0 or ( [Status] = 'B' or [Status]='M' and ([Deliveryvalue]= 0 and Deliveryposition ='1'), Dateofdelivery.autoCalendar.Date))
But I still don't get what I want. I think its either the syntax or its only possible within a aggregation á la set analysis? Please note I want a dimension not a measure.
I hope someone has the answer.
Thank you very much in advance!
Hi @Applicable88, yes, it's different, in that case if ([Status] = 'B'or [Status]='M') is true it will return true for all the comparison, that will be the same as ([Status] = 'B'or [Status]='M') or (Deliveryposition ='1' and ([Deliveryvalue]<> 0)).
Use parethesys to group the comparisons as needed, I do it even if it's not necessary to clarify understanding of the comparison.
To make it with set analysis you probably need aggr, it could be:
=Aggr(Only({<Status={'B','M'}>*(<Deliveryposition={'1'}>+<Deliveryvalue={">0<0"}>)} Dateofdelivery.autoCalendar.Date), Dateofdelivery.autoCalendar.Date)
Hi, can you try this?:
=if(([Status] = 'B'or [Status]='M') and ([Deliveryvalue]<> 0 or Deliveryposition ='1'), Dateofdelivery.autoCalendar.Date)
Hi @rubenmarin,
thanks for the quick reply. Does it makes a difference if the "AND" between status M and Deliveryvalue is there,
or would this be different: =if(([Status] = 'B'or [Status]='M') or Deliveryposition ='1' and ([Deliveryvalue]<> 0 ), Dateofdelivery.autoCalendar.Date)
And is there also a way to write it like set analysis, even though its a dimension of a table and not a measure?
Thanks in advance!
Hi @Applicable88, yes, it's different, in that case if ([Status] = 'B'or [Status]='M') is true it will return true for all the comparison, that will be the same as ([Status] = 'B'or [Status]='M') or (Deliveryposition ='1' and ([Deliveryvalue]<> 0)).
Use parethesys to group the comparisons as needed, I do it even if it's not necessary to clarify understanding of the comparison.
To make it with set analysis you probably need aggr, it could be:
=Aggr(Only({<Status={'B','M'}>*(<Deliveryposition={'1'}>+<Deliveryvalue={">0<0"}>)} Dateofdelivery.autoCalendar.Date), Dateofdelivery.autoCalendar.Date)