Set Analysis With Max Function Dependent on Three Fields

[#1]

I have the following dataset:

Name	Stage	Target Date	Status
Item 1	One	1/1/2015	Green
Item 1	Two	2/3/2015	Yellow
Item 1	Three	4/5/2015	Green
Item 1	Three	6/7/2015	Green
Item 2	Two	2/10/2015	Red
Item 3	One	3/1/2015	Green
Item 3	Two	3/15/2015	Green
Item 3	Three	4/2/2015	Green
Item 3	Three	5/3/2015	Yellow
Item 3	Three	6/4/2015	Yellow
Item 3	Three	8/9/2015	Red

I am looking for a set analysis expression to return the Status corresponding to the Max(Target Date) for each Stage for each Name:

The result table should look like this:

Name	One Date	One Status	Two Date	Two Status	Three Date	Three Status
Item 1	1/1/2015	Green	2/3/2015	Yellow	6/7/2015	Green
Item 2			2/10/2015	Red
Item 3	3/1/2015	Green	3/15/2015	Green	8/9/2015	Red

I have tried the following but it does not quite work:

Dimensions: Name

Expressions:

One Date: =Max({<[Stage]={'One'}>} [Target Date])

One Status: =Only({<[Target Date]={"$(=Max({<[Stage]={'One'}>} [Target Date]))"} >} [Status])

Two Date: =Max({<[Stage]={'Two'}>} [Target Date])

Two Status: =Only({<[Target Date]={"$(=Max({<[Stage]={'Two'}>} [Target Date]))"} >} [Status])

Three Date: =Max({<[Stage]={'Three'}>} [Target Date])

Three Status: =Only({<[Target Date]={"$(=Max({<[Stage]={'Three'}>} [Target Date]))"} >} [Status])

I have experimented with the Aggr function but to no avail.

What am I missing?

Thanks!

[reshmakala]

Can you try like this?

One Date = $(=Max({<[Stage]={'One'}>} [Target Date])) ??

[#3]

The issue is obtaining the correct Status field for the Max(Target Date). I am able to get the Max(Target Date) for the XXX Date field expressions. What I cannot get, reliably, are the Status fields XXX Status.

[jerem1234]

Used a Straight table with Name as Dimension and expressions for each 1, 2, and 3 similiar to:

One Date:

date(Max({<[Stage]={'One'}>} [Target Date]))

One Status:

firstsortedvalue({<[Stage]={'One'}>}Status,-[Target Date])

Please find attached as well.

Hope this helps!

[#5]

Well, it *almost* works. I am getting some NULL values with my real dataset using the FirstSortedValue statement. There does not seem to be a pattern as to when it returns or does not return a null Status. I need to check the dates to see if there are duplicate Target Dates which may be influencing the expression. Some of the Status are returned correctly. Some are not.

The Max(Target Date) seems to return the correct value in each instance. It is only the Status field which returns null in some instances when there is a real value present.

[jerem1234 · Accepted solution]

When there are duplicate dates, then firstsortedvalue doesnt know which one to return (since they are equally ranked). So it will return null. If you want some value to return, such as the first one in your data, then you can add distinct in your expression like:

firstsortedvalue(distinct {<[Stage]={'One'}>}Status,-[Target Date])

Note: This will make the expression turn red and make it look like its incorrect, but it is correct. You can use the distinct keyword in for firstsortedvalue.

Hope this helps!

[#7]

DISTINCT did it! Thanks!