Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
blunckc1
Creator
Creator

Set analysis with firstsortedvalue

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

2 Replies
sunny_talwar

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
Creator
Creator
Author

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