Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
nburton78
Creator
Creator

find the min of a dimension based on another column in a straight table

Hello,

This is what I'm trying to create using a straight table in Qlikview

 20 min 20 max30 min
2441/2/20201/8/20201/9/2020

 

My data is coming from a ledger file that notes an order status and the day it is updated.  The statuses are not always in order (20,30) sometimes an order will get to a status and revert back to a previous status. 

Order_NumberStatus_CodeUpdated_Date
244201/2/2020
244301/3/2020
244201/7/2020
244201/8/2020
244301/9/2020
244301/10/2020

 

I calculated the min and max date an order reached a 20 status.  the min 30 status should be the min date that an order hit a 30 status when the updated date is greater than or equal to the max date the order reached a 20 status.  

the formula I'm using is "min({<[Status_Code]={30}, [Updated_Date]>=max20>}[Updated_Date])" which gives me an "error in set modifier expression" 

Any suggestions on how to make this work?  I've attached a sample.  Thank you

Labels (1)
2 Replies
jwjackso
Specialist III
Specialist III

Try

Min({<Status_Code={'30'},Updated_Date={">=$(=Max({<Status_Code={'20'}>}Updated_Date))"}>}Updated_Date)

 

nburton78
Creator
Creator
Author

Sorry I thought that was the solution, unfortunately when I added more data to the sample I ran into an issue.  t The formula finds the max date for the whole data set not the dimension.  I attached the updated sample with more lines.