Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Amberj_29
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
sunny_talwar

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
sunny_talwar

May be this

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

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

sunny_talwar

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

Amberj_29
Contributor III
Contributor III
Author

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

Many thanks!