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

[Anonymous]

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.

Name	EEID	Shift 1	Shift 2	Shift 3
Tom	49985	6/15/16	6/17/16	6/19/16
Sally	52636	6/25/16	6/27/16	6/29/16
David	77436	6/27/16		6/30/16
Tina	78895	6/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

[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 · Accepted solution]

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]

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]

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