Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shempgracie
Contributor III
Contributor III

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

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

 

Labels (4)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

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

View solution in original post

6 Replies
Lisa_P
Employee
Employee

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

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

shempgracie
Contributor III
Contributor III

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

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

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

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

View solution in original post

shempgracie
Contributor III
Contributor III

Thank you Lisa!  That worked perfect!!!

 

Greatly appreciated!

Mike