Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
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.

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