Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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

      

ReqNumberCurrent StepStep OrderCurrent Step DateFurthest Step (What I currently get)Furthest Step (How I want it to look)
12345Inbox03/15/2016 InboxSchedule Interview
12345Candidate Review53/18/2016 Candidate ReviewSchedule Interview
12345Hiring Manager Review113/18/2016 Hiring Manager ReviewSchedule Interview
12345Candidate Slate63/21/2016 Candidate SlateSchedule Interview
12345Schedule Interview123/21/2016 Schedule InterviewSchedule Interview
12345Inbox03/21/2016 InboxSchedule Interview
2 Replies

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

please post a sample application

thanks

regards

Marco

manucamon
Valued Contributor III

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

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!