How to attach the most current step in a process to each record in a straight table

Sun, 04 Sep 2016 16:09:06 GMT

Hello all,

I am developing a recruiting application and am still learning about how Qlikview behaves. I created a straight table that shows some basic transaction data. The table below shows the recorded step in the recruitment process on each day. What I'd like to do is show the furthest step inn the recruitment process for a job based on every transaction up until the selected date. For example, If the user selects 3/18/2016, the most recent step should be 'Hiring Manager Review' (based on 'Step Order' field). Here is the formula I've come up with so far:

Furthest Step = FirstSortedValue({$<[Current Step Date]={"<=$(=$(vSelectedDate))"}>}DISTINCT [Current Step],-[Step Order])

It doesn't work the way I was hoping. What I would like to see is the result in the last column. This assumes the user has selected 3/21/2016. It looks like the formula is only evaluating at the row level. How can I make it evaluate the records that are before the selected date?

Thanks

ReqNumber	Current Step	Step Order	Current Step Date	Furthest Step (What I currently get)	Furthest Step (How I want it to look)
12345	Inbox	0	3/15/2016	Inbox	Schedule Interview
12345	Candidate Review	5	3/18/2016	Candidate Review	Schedule Interview
12345	Hiring Manager Review	11	3/18/2016	Hiring Manager Review	Schedule Interview
12345	Candidate Slate	6	3/21/2016	Candidate Slate	Schedule Interview
12345	Schedule Interview	12	3/21/2016	Schedule Interview	Schedule Interview
12345	Inbox	0	3/21/2016	Inbox	Schedule Interview

Re: How to attach the most current step in a process to each record in a straight table

MarcoWedel — Sun, 04 Sep 2016 21:57:50 GMT

please post a sample application

thanks

regards

Marco

Re: How to attach the most current step in a process to each record in a straight table

Anonymous — Mon, 05 Sep 2016 07:49:10 GMT

Hi jason,

Try this

=MaxString(TOTAL <ReqNumber> {1<[Current Step Date]={'$(=Max([Current Step Date]))'},

[Step Order] = {"=$(=Max({$<[Current Step Date]={'$(=Max([Current Step Date]))'}>}[Step Order]))"}

>} [Current Step])

Regards!

topic Re: How to attach the most current step in a process to each record in a straight table in QlikView

How to attach the most current step in a process to each record in a straight table

Re: How to attach the most current step in a process to each record in a straight table

Re: How to attach the most current step in a process to each record in a straight table