topic Re: how to do firstsortedvalue(sum(sales),date)? in QlikView

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

Wed, 11 Mar 2015 08:03:56 GMT

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.

anbu1984 — Wed, 11 Mar 2015 08:12:29 GMT

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

tresB — Wed, 11 Mar 2015 08:18:10 GMT

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

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

chrislofthouse — Wed, 11 Mar 2015 08:21:14 GMT

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 — Wed, 11 Mar 2015 08:25:59 GMT

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