topic Re: Display Complete History of Next Value in List in QlikView

Display Complete History of Next Value in List

ciaran_mcgowan — Wed, 05 Oct 2016 15:02:58 GMT

Hi,

I have an interesting problem where I need to show a list of how an employee has performed in a certain type of evaluation. My client regularly sets tasks/tests for employees to complete during the course of a year. These are then displayed in a list along with the date have have or are scheduled to take them:

Date	Employee	Task
01/02/2016	John	Bi-Monthly Ethics Exam
25/02/2016	John	Self Assessment
01/03/2016	John	Bi-Monthly Ethics Exam
15/05/2016	Paul	Entrant Exam
01/06/2016	Paul	Bi-Monthly Ethics Exam

I have another table where a list of employees is displayed but instead of all their tasks, it only shows their next on. I did this using the formula:

FirstSortedValue({<Flag.UpcommingExam={1}>} Exam, Date)

What I want to know is it possible to create a table that ONLY shows a history of their next exam? I've tried a few variations of set analysis but not been successful so far. I would like to be able to select an employee and the table show their next exam along with their history ONLY for that exam, like below:

Date	Task	History
01/01/2016	Bi-Monthly Ethics Exam	Pass
01/02/2016	Bi-Monthly Ethics Exam	Fail
01/03/2016	Bi-Monthly Ethics Exam	Pass
01/04/2016	Bi-Monthly Ethics Exam	Pass
01/05/2016	Bi-Monthly Ethics Exam	Not Done

Re: Display Complete History of Next Value in List

sunny_talwar — Wed, 05 Oct 2016 15:05:47 GMT

May be it would be helpful for us to take a look at the qvw you are trying this out on. I think by looking at it, we might be able to offer better solutions.

Re: Display Complete History of Next Value in List

ciaran_mcgowan — Wed, 05 Oct 2016 15:20:41 GMT

Sorry Sunny,

Can't use the actual qwv so I'll see if I can replicate with some dummy data.

Re: Display Complete History of Next Value in List

sunny_talwar — Wed, 05 Oct 2016 15:23:52 GMT

May be this link can help

Preparing examples for Upload - Reduction and Data Scrambling

Re: Display Complete History of Next Value in List

ciaran_mcgowan — Wed, 05 Oct 2016 15:59:38 GMT

I've added a sample qvw. Same principle, just different results.

Re: Display Complete History of Next Value in List

sunny_talwar — Wed, 05 Oct 2016 17:17:59 GMT

This?

You had a misplacement of = sign in your expressions:

Only({<Exam = {"$(=FirstSortedValue({<Flag.FutureExam={1}>} Exam, Date))"}>} Exam)

Only({<Exam = {"$(=FirstSortedValue({<Flag.FutureExam={1}>} Exam, Date))"}>} Result)

Or you can try this as well:

Dimensions

Employee

Date

=Aggr(If(FirstSortedValue(TOTAL {<Flag.FutureExam={1}>} Exam, Date) = Exam, Exam), Employee, Date)

Expression

Result

Re: Display Complete History of Next Value in List

ciaran_mcgowan — Wed, 05 Oct 2016 23:00:18 GMT

Thanks again Sunny!

I can't believe it was that simple! You're alternative solution also works great.