Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
keithlawrence
Contributor III
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:

LOAD * INLINE [

    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
MK_QSL
MVP
MVP

FirstSortedValue(DISTINCT Flag,Temp)

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

View solution in original post

3 Replies
YoussefBelloum
Champion
Champion

Hi,

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

MK_QSL
MVP
MVP

FirstSortedValue(DISTINCT Flag,Temp)

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

View solution in original post

keithlawrence
Contributor III
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