Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dpage
Contributor III
Contributor III

Sort by Expression

Hello,

 I am trying to sort by expression. My dimensions are Item.Full Name,  Item.Sales description, and Item.Preferred Vendor Full Name. 

The expressions calculate Qty. on Hand, 1 shows the Reorder Point and 1 shows the Build Point (depending on the Item Type) and then I have a column for the avg usage.

I created a sort by expression that sorts the first dimension Item.Full Name

If([Item.Reorder Point]<=[Item.Quantity On Hand],1,If([Item.Build Point]<=[Item.Quantity On Hand],2,If(IsNull([Item.Reorder Point] or [Item.Build Point]),3)))

The idea is to show the items that are low first (the Qty. on Hand value turns red if < or = to the reorder or build point, whichever applies) and then to show the rest with the Qty. on Hand value in green.

However, when I scroll through, it starts out perfect, all red, but then I see a whole bunch of green and red quantities interspersed throughout the table. Can't discern why. According to the table, the Qty. on Hand is clearly more than the reorder or build point and items lower down, the QOH is = or < the reorder or build point.

Let me know if you need more clarity.

Thanks!

Labels (3)
4 Replies
m_woolf
Master II
Master II

One thing I see tht is wrong with your expression:

If([Item.Reorder Point]<=[Item.Quantity On Hand],1,If([Item.Build Point]<=[Item.Quantity On Hand],2,If(IsNull([Item.Reorder Point]) or isnull([Item.Build Point])),3)))

Dpage
Contributor III
Contributor III
Author

Thanks for pointing that out. I had fixed it but am still not getting the result I want. 

Dpage
Contributor III
Contributor III
Author

This is what I have now    If([Item.Reorder Point]<=[Item.Quantity On Hand],1,If([Item.Build Point]<=[Item.Quantity On Hand],2,If(IsNull([Item.Reorder Point] or [Item.Build Point]),3)))

Brett_Bleess
Former Employee
Former Employee

This is a duplicate thread, please see the original thread that has further comments:

https://community.qlik.com/t5/New-to-QlikView/Sort-by-Expression/m-p/1609971/highlight/false#M378943

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.