Display Complete History of Next Value in List

[ciaran_mcgowan]

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

[sunny_talwar]

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.

[ciaran_mcgowan]

Sorry Sunny,

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

[sunny_talwar]

May be this link can help

Preparing examples for Upload - Reduction and Data Scrambling

[ciaran_mcgowan]

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

[sunny_talwar · Accepted solution]

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

[ciaran_mcgowan]

Thanks again Sunny!

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