Qlik Community

Ask a Question

New to QlikView

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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))

MVP
MVP

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

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

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.

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