Another question about a summary table from a new user

[glm3]

I am relatively new and have now graduated form simple data models and charts to something a little more complicated and am struggling to get data displayed correctly.  This is a simple representation of my data, which I am using in an attempt to create a summary of the data in the first figure.

Data

Desired Summary Display

I use a table with a dimension for the [Student ID], but struggling with the current status field attempting to use various combinations of the Only and Aggr functions with the following set analysis.

{<Stage={"=Min(Stage)}>}Status for initial status and {<Stage={"=Max(Stage)}>}Status for current status.

For example neither of the following work to create a calculated measure for the Current Status Status.

=Only({<Stage={"=Max(Stage)"}>}Status)

=aggr ({<Stage={"=Max(Stage)"}>}Status, StudentID)

Any hints or recommendations to create a calculated measure for Stage Status would be greatly appreciated.

Thanks

[GaryGiles]

I've struggle with a few similar scenarios.  In the end, it was easiest to create a flag via the load scripts.  So, you might want to consider creating a Current_Stage flag and an Initial_Stage flag in the load scripts.  Then your set analysis becomes quite straightforward and easy.  Current_Stage={1} or Initial_Stage={1}.  There are many times you can pre-calculate fields and flags in the load script to make your expressions more flexible and efficient.

There are a couple of ways to create such a flag in the load script.  A basic example for the Current_Stage is below.  The example assumes one table called StudentData, like your example.  Your actual data model may be more complex, so you may have to adjust the table that you add the flags to and how you identify the unique rows in your join.

TempTable:
Load [Student ID],
max(Stage) as Stage
Resident StudentData
Group by Group, [Supplier ID];

Join (StudentData)
Load [Student ID],
Stage,
1 as Current_Stage_Temp
Resident TempTable;

Join (StudentData)
Load [Student ID],
Stage,
if(isnull(Current_Stage_Temp),0,1) as Current_Stage
Resident StudentData;

drop field Current_Stage_Temp;

drop table TempTable;

[GaryGiles · Accepted solution]

Upon further review . . .

While altering the load script will work, there is an approach accomplish this in a chart/table:

For Current Stage, you could use:

=aggr(FirstSortedValue(Stage,-Stage),StudentID)

Current Start Date:

=Date(aggr(FirstSortedValue([Start Date],-Stage),StudentID),'MM/DD/YYYY')

Current Status:

=aggr(FirstSortedValue(Status,-Stage),StudentID)

Initial Stage:

=aggr(FirstSortedValue(Stage,Stage),StudentID)

Initial Stage Start Date:

=Date(aggr(FirstSortedValue([Start Date],Stage),StudentID),'MM/DD/YYYY')

Initial Stage End Date:

=Date(aggr(FirstSortedValue([End Date],Stage),StudentID),'MM/DD/YYYY')

Initial Stage Score:

=aggr(FirstSortedValue([Score],Stage),StudentID)

[glm3]

Thank you very much! For my dataset the second option using FirstSortedValue worked best, but both are great options for my toolbox!