Scenario:
We need to find the countries who have the MAX and MIN sales and store them in variables to use in set analysis.
Solution:
FIRSTSORTEDVALUE function can sort the fields according to a sort-weight (another field or an aggregation) and return the corresponding value of the field.
In our case we need to sort the countries by their total sales, thus we will use AGGR(sum(sales),country) as our sort-weight in FIRSTSORTEDVALUE function:
vCountryWithMaxSales =FirstSortedValue(Country, -Aggr(sum(Sales),Country))
The above expression returns the country which has the MAX sales.
By default, the FIRSTSORTEDVALUE function sorts in ascending order. For sorting in descending order, we use the minus sign in front of the sort-weight.
So, if we would like to get the country which has the MIN sales, we can just remove the minus sign:
vCountryWithMinSales =FirstSortedValue(Country, Aggr(sum(Sales),Country))
Warning:
If more than one country have the same MAX or MIN sales, then the FIRSTSORTEDVALUE function will return NULL. To avoid this, we can use DISTINCT qualifier:
vCountryWithMinSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))
Sample:
https://sites.google.com/site/quickdevtips/home/findmax.qvw?attredirects=0&d=1
Thank you, I solved my problem.
ReplyDeleteIf I have the dimension of customer, and the two measures of Sales value and Date.
ReplyDeleteHow in a table grouped at Customer level can I show the minimum date WHERE the sales value is the maximum, ie the same sales value could repeat across dates and I want the earliest that the max has occured.
I have tried to manipulate an AGGR function but to no avail
SMM PANEL
ReplyDeletesmm panel
İs İlanlari Blog
İnstagram takipçi satın al
Https://www.hirdavatciburada.com/
WWW.BEYAZESYATEKNİKSERVİSİ.COM.TR
Servis
tiktok jeton hilesi
Good content. You write beautiful things.
ReplyDeletevbet
sportsbet
mrbahis
korsan taksi
hacklink
mrbahis
vbet
taksi
sportsbet
Good article text write content successfull... thanks.
ReplyDeletebetpark
slot siteleri
bonus veren siteler
kibris bahis siteleri
kralbet
betmatik
poker siteleri
tipobet
muş
ReplyDeletetokat
afyon
amasya
manisa
BN206
https://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
DZ71G
https://izmitone.com
ReplyDeletekuşadası
sex hattı
5UT