topic Re: Set analysis with firstsortedvalue in QlikView

Set analysis with firstsortedvalue

blunckc1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis with firstsortedvalue

sunny_talwar — Thu, 29 Sep 2016 15:59:00 GMT

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

Re: Set analysis with firstsortedvalue

blunckc1 — Thu, 29 Sep 2016 23:46:10 GMT

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