Newbie question on using AGGR with a filter for a specific year

[shempgracie]

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

[Lisa_P]

If you have the year field, you can use set analysis as below

FirstSortedValue(State, aggr(<{Year={2018}>}sum([Paid Out])),State))

[shempgracie]

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

[Lisa_P]

You could try a date range....
FirstSortedValue(State, aggr(<{[Start Date]={">Date1<Date2"}>}sum([Paid Out])),State))

If you have access to the script, you could create a year field using the year function

[shempgracie]

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

[Lisa_P · Accepted solution]

Sorry, I can see a syntax issue, try this:
FirstSortedValue(State, aggr(sum({<[Start Date]={">12/31/2018<01/01/2020"}>}[Paid Out]),State))

[shempgracie]

Thank you Lisa!  That worked perfect!!!

Greatly appreciated!

Mike