Hi,
How would you approach this scenario? I'm working with Covid19 data and currently I would like to use firstsortedvalue to print the second worst affected US state by cases and its case number.
I'm getting the worst affected state and its correct case count done with this. Note that data is a few weeks old.
'Most Affected State by Total Cases is: ' & firstsortedvalue([Counties.state],-Aggr(sum([Counties.cases]), [Counties.state])) & ' with a total of this many cases: ' & max(Aggr(sum([Counties.cases]), [Counties.state]))
But how can I go about setting up the same second worst state and case count? I know I can use rank in firstsortedvalue which results in New Jersey but how can I pass New Jersey dynamically into the set analysis that calculates the sum of cases?
Trying like this:
'Second Most Affected State by Total Cases is: ' &
firstsortedvalue([Counties.state],-Aggr(sum([Counties.cases]), [Counties.state]),2)& ' with a total of this many cases: ' & sum({<[Counties.state] = {"=$(firstsortedvalue([Counties.state],-Aggr(sum([Counties.cases]), [Counties.state]),2))"}>}[Counties.cases])
Do I need to use rank in the set analysis for cases?
