Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

how to do firstsortedvalue(sum(sales),date)?

e.g. my table looks like this

region     date               sales

ASIA       2015.03.01     100    
ASIA       2015.03.01     200

ASIA       2015.03.02     300

ASIA       2015.03.02     400

I want to get the total sales for most recent days:

region     sales

ASIA      700

something like:

firstsortedvalue(sum(sales),date)

but it doesn't work.

4 Replies
Highlighted
Master III
Master III

In Script,

Load region,Firstsortedvalue(date,-date),Firstsortedvalue(sales,-date) Group by region;

Load region,date,sum(sales) as sales Group by region,date;

Load region,Date#(date,'YYYY.MM.DD') as date,sales Inline [

region,date,sales

ASIA,  2015.03.01,100

ASIA,  2015.03.01,200

ASIA,  2015.03.02,300

ASIA,  2015.03.02,400 ];

In Chart,

Sum(If(date=Aggr(Max(date),region),sales))

Highlighted
MVP
MVP

Try using advanced aggregation(required when using aggregation function within another one), like:

firstsortedvalue(Aggr(sum(sales), date) ,-date)

Highlighted
Luminary
Luminary

Like attached, I wasn't sure if your date was coming in like you listed in your example so I converted it to a date.

Highlighted
Luminary
Luminary

To add first sorted value to get the region with the highest sales for the latest date would be:

=FirstSortedValue(region,-aggr(sum({<DateValue={"$(=Max(DateValue))"}>}sales),region))