changing exp into a calculated dimension

[sindhura]

I have a status field as expression  in a straight table which contains

=if(CancelledDate<>Null(),'Cancelled'

,if(_TriageDate<>Null(),'Processed'

,if(_Rctp_Date<>Null() ,'Received'

,if(Len(ord.ShipDate)=0,'InStore','Shipped'))))

and my users want to filter that one.

so i changed it to calculated dimension, but after changing to calculated dimension, i find nulls in place of instore. why is this happening

[sunny_talwar]

Not sure I understand the issue. Do you have a sample you can share?

[marcus_sommer]

A possible reason might be that you have sometimes more then one unique date-value (maybe several different ship-dates for one order) for your dimension-values and your check contained no aggregation - only field = value. Is there more then one unique value NULL will be returned. This meant you need to apply an aggregation or an error-handler with alt(), maybe:

Len(max(ord.ShipDate)) // required an additional aggr-wrapping within a dimension

     or

alt(Len(ord.ShipDate), 0)

- Marcus

[jonathandienst]

This part of the expression is incorrect. It will always return false (in expression or as a dimension):

  CancelledDate<>Null()

The correct way to look for nulls is

    IsNull(CancelledDate)

    or

     Len(CancelledDate) = 0

[jagan]

HI,

Try like this

=if(Not IsNull(CancelledDate),'Cancelled'

,if(Not IsNull(_TriageDate),'Processed'

,if(Not IsNull(_Rctp_Date) ,'Received'

,if(Len(Trim(ord.ShipDate))=0,'InStore','Shipped'))))

Regadrs,

jagan.

[sindhura]

Thanks all for responding me. I have with all the ways you people were suggesting but nothing helped out.

I was able to see all the status except the default one i.e "Instore".

[sindhura]

See in the image I can see Instore in the expression and in the text object but not in Listbox or in calculated Dimension eventhough the expression is same

[marcus_sommer]

I think you should provide a sample.

- Marcus

[sindhura]

Hi Everyone,

Thanks for helping with the possible chances.

I have used flags in the scripting  like

if(Len(Trade_ShipDate)<>0,1,0) as _ShippedDateFlag

     if(CancelledDate<>Null(),1,0) as _CancelledDateFlag,

     if(TriageDate<>Null(),1,0) as _ProcessedDateFlag,

if(Rctp_Date<>Null(),1,0) as _ReceivedDateFlag,

and changed the expression as

if(_CancelledDateFlag=1,'Cancelled'

,if(_ProcessedDateFlag=1,'Processed'

,if(_ReceivedDateFlag=1 ,'Received'

,if(_ShippedDateFlag=1,'Shipped'

,'InStore'))))

Finally it worked.

Thanks for the response and keep Helping:):)

[jagan]

Hi,

If you got the Answer please close this thread by giving Correct and Helpful answers.

Regards,

jagan.