Set analysis with firstsortedvalue

[blunckc1]

Hi everyone,

I currently have this FirstSortedValue formula - =FirstSortedValue(aggr(Sum(WeeklyHours),ReferenceDateTwo),Aggr(-ReferenceDateTwo,ReferenceDateTwo))

I now need to show this result for a two very distinct groups on a single chart so there results can be compared to one another.  I believe set analysis is the way to go but I'm really unsure how to write it.  Have been using the set analysis wizard but I am stumped!

The conditions are:

Group 1

Select records equal to "Anaesthesia SMO, Anaesthetics Junior Medical" in field "Org Unit Title"

Group 2

Select records equal to "Division of Surgery & Perioperative Serv" in field "Division" AND

Select records equal to "Junior Medical, Senior Medical" in field "ProfessionalStream"

If someone knows how to write that all in a set analysis you are a genius!

Thanks in advance.

Carl

[sunny_talwar · Accepted solution]

May be these

1) =FirstSortedValue({<[Org Unit Title] = {'Anaesthesia SMO', 'Anaesthetics Junior Medical'}>}Aggr(Sum({<[Org Unit Title] = {'Anaesthesia SMO', 'Anaesthetics Junior Medical'}>}WeeklyHours),ReferenceDateTwo),Aggr(-ReferenceDateTwo,ReferenceDateTwo))

2) =FirstSortedValue({<Division = {'Division of Surgery & Perioperative Serv'}, ProfessionalStream = {'Junior Medical', 'Senior Medical'}>}Aggr(Sum({<Division = {'Division of Surgery & Perioperative Serv'}, ProfessionalStream = {'Junior Medical', 'Senior Medical'}>}WeeklyHours),ReferenceDateTwo),Aggr(-ReferenceDateTwo,ReferenceDateTwo))

[blunckc1]

Legend!!! Works perfectly.  Really appreciate the assist.