Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

FirstSortedValue Help in text box

Hi All, I have a very simple task that I cannot solve, any help would be great.

I am using this formula and it works a charm for text results:

FirstSortedValue( [Product_Type], -aggr( sum([Net Val]), [Product_Type_Matchup]) )

However, what I want to do is the result of the above I also want the net value of the text result. For example if the first sorted value says 'Digital' I then want to have the sum of the net value of 'Digital'. Also these are dynamic KPIs in a text box, so I cannot do a simple 'if match' function. 

1 Solution

Accepted Solutions
Highlighted

This is what you tried?

FirstSortedValue([Product_Type], -Aggr(Sum([Net Val]), [Product_Type_Matchup]))
& ': ' &
Num(Max(Aggr(Sum([Net Val]), [Product_Type_Matchup])), '£#,##0', '.', ',')

View solution in original post

4 Replies
Highlighted

May be this

FirstSortedValue([Product_Type], -Aggr(Sum([Net Val]), [Product_Type_Matchup]))
& ': ' &
Max(Aggr(Sum([Net Val]), [Product_Type_Matchup]))
Highlighted
Contributor III
Contributor III

Thank you so much! This is exactly what I wanted. 😀

Before I mark your response as the solution, is there any way to have 'Net value' formatted as currency as it displays as just a number and I would prefer it formatted if possible. I tired adding in ,'£#,##0', '.',',') but it doesn't seem to fit in. Many thanks!!!

Highlighted

This is what you tried?

FirstSortedValue([Product_Type], -Aggr(Sum([Net Val]), [Product_Type_Matchup]))
& ': ' &
Num(Max(Aggr(Sum([Net Val]), [Product_Type_Matchup])), '£#,##0', '.', ',')

View solution in original post

Highlighted
Contributor III
Contributor III

Thank you... yeah I didn't add it to the end I added '£#,##0', '.',','  after the net value. 

Many thanks!