Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Contributor III

## Set Expression using formula

I'm struggling with this for quite a while now and after searching the Community I've come to the conclusion it's not a situation that's been asked before.

I have a list of temperatures in a month and a field flag. I need to be a pull through the flag for the lowest temp in each month. No matter what I try it seems to only pull through the flag on the lowest temp for the entire data rather than by each individual month. My basic data is as such:

Month, Temp, Flag, Country

1, 10, 1, AA

1, 11, 0, AA

1, 15, 0, BB

2, 11, 0, CC

2, 15, 1, CC

3, 10, 0, AA

3, 9, 1, BB

3, 15, 0, BB

4, 16, 0, CC

4, 9, 0, CC

4, 10, 1, AA

];

I can get the min temp per month by simply using Min(Temp). What I need is to know the flag for that min temp per month. I've tried the following with no success:

only({<Temp={\$(=min(Temp))}>}Flag)

only({<Temp={'\$(=min(Temp))'}>}Flag)

only({<Temp={'\$(=Aggr(min(Temp)),Month)'}>}Flag)

Aggr(only({<Temp={'\$(=min(Temp))'}>}Flag),Month)

I basically get the flag working on month 3 and 4 as they are both min temp 9 and that is the overall min temp.

How do I get it to work for the individual months?

My next problem is establishing what country had that temp but if I can't fix the above, I can't get the country.

Any ideas?

1 Solution

Accepted Solutions
MVP

FirstSortedValue(DISTINCT Flag,Temp)

Don't forget to untick suppress Zero values from Presentation tab.

3 Replies
Champion

Hi,

can you just simply describe what is the expected output with the data shown above ? via an image for example

MVP

FirstSortedValue(DISTINCT Flag,Temp)

Don't forget to untick suppress Zero values from Presentation tab.

Contributor III
Author

It's quite annoying that it was as simple as using a FirstSortedValue!! It works perfectly. I really should have picked that up

Community Browser