Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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
anbu1984
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))

tresesco
MVP
MVP

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

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

chrislofthouse
Partner Ambassador
Partner Ambassador

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.

chrislofthouse
Partner Ambassador
Partner Ambassador

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