Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
Try using advanced aggregation(required when using aggregation function within another one), like:
firstsortedvalue(Aggr(sum(sales), date) ,-date)
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.
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))