Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sindhura
Contributor II
Contributor II

changing exp into a calculated dimension

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

9 Replies
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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jagan
Partner - Champion III
Partner - Champion III

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

status.PNG

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
Contributor II
Contributor II
Author

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
Partner - Champion III
Partner - Champion III

Hi,

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

Regards,

jagan.