Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

First Selected Value - Second, Third, Fourth, etc values

Hello,

I'm working to keep track of the days my trainees have each of their training shifts.  Each trainee is expected to complete a certain number of training shifts and our daily shift history reload contains all shifts published 'tomorrow' so that Qlik is always receiving the most up to date schedule prior to that day starting.

This is the desired output.

NameEEIDShift 1Shift 2Shift 3
Tom499856/15/166/17/166/19/16
Sally526366/25/166/27/166/29/16
David774366/27/166/30/16
Tina788956/29/16

I've pulled the first shift date using this expression:

=if(Shift_ShiftTypeName='Trainee',(FirstSortedValue(Shift_StartDateTime,Shift_StartDateTime)))

Using that syntax, I tried this to try and get the 4th record:

=if(Shift_ShiftTypeName='Trainee',(FirstSortedValue(Date(Shift_StartDateTime),Shift_StartDateTime,4)))

Am I going about this the right way?  For some of my users, their records pull up just fine and in order.  For some of the people, it will show a record for Shift 1, 2, nothing for shift 3, 4, then have a record for shift 5.  I don't really understand how it can know what record 1, 2, and 5 should be without knowing what records 3 and 4 are.

Thank you for the direction, happy to provide any further details.

Phil

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Try like below,

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

View solution in original post

4 Replies
sunny_talwar

Why don't you try with Max instead of FirstSortedValue:

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 2))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 3))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 4))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 5))

tamilarasu
Champion
Champion

Try like below,

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

Anonymous
Not applicable
Author

Thank you for the help, I tried this instead and it worked as intended.

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

tamilarasu
Champion
Champion

Yes. You are right.  We need to use Min in this case as per your sample. Have a nice day.