Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Sense Community,
I am newbie and want to have a text box that tells me the State that generated the highest revenue for a specific year.
My data looks like the below. I'm looking for an expression that would give me the highest revenue state for the year 2018 (in this case it would be New York).
Virginia 2018 25,000
Virginia 2019 17,000
Maryland 2018 45,000
New York 2018 55,000
Any help on this would be greatly appreciated! I tried the below and its not working.
FirstSortedValue(State, aggr(if(Year([Start date])=2019,sum([Paid Out])),State))
Thanks!
Mike
If you have the year field, you can use set analysis as below
FirstSortedValue(State, aggr(<{Year={2018}>}sum([Paid Out])),State))
Thanks Lisa! Unfortunately I don't have a year field. I have a date field. I tried to use your formula and put Year([Start Date]), but it doesn't seem to return any value.
Any other thoughts on what I could do?
Thanks!
Mike
Hi Lisa,
Thank you for the suggestion. I tried the date range and it still didn't return anything. I did this to try and get the 2019 year:
=FirstSortedValue(State, aggr(<{[Start Date]={">'12/31/2018'<'01/01/2020'"}>}sum([Paid Out]),State))
I also tried the dates with and without the single tick quotes.
Any thoughts on what I'm doing wrong?
Thank you!
Mike
Thank you Lisa! That worked perfect!!!
Greatly appreciated!
Mike