
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FirstSortedValue from an Aggr Measure
Hello,
I have the following data in Qlik Sense.
Dimension | Measures | ||
Entry Month\Year | Rate | Sales Qty | Count |
Jan-2019 | 0.00108041 | 41692 | 45 |
Feb-2019 | 0.001354416 | 27586 | 55 |
Mar-2019 | 0.001140416 | 45236 | 29 |
Apr-2019 | 0.00110921 | 56928 | 59 |
May-2019 | 0.001060559 | 40125 | 38 |
Jun-2019 | 0.001054936 | 35515 | 37 |
Jul-2019 | 0.000986442 | 37502 | 31 |
I'm attempting to retrieve the last months Sales Qty. To retrieve the last month I used the following expression:
=FirstSortedValue([MonthYear],-aggr(Distinct([MonthYear]),[MonthYear],[Year]),1)
However, I'm not able to use the same expression and substitute for my Sales Qty when I use the following:
=FirstSortedValue([Sales Qty],-aggr(Sum([Sales Qty]),[MonthYear],[Year]),1)
The above return a number 5, which does not align with any of my data sets. I have 7 months in my data and Sales are in the thousands.
I typically use Sales Qty from a known variable v_Sales.Qty or from a variable with a set of data, so I've tried:
=FirstSortedValue([Sales Qty],-Aggr( Sum( {< $(v.ControlPeriod.Rate.Set) >} [Sales Qty]), [MonthYear],[Year]),1 )
or
=FirstSortedValue( {< $(v.ControlPeriod.Rate.Set) >}
Aggr( Sum( {< $(v.ControlPeriod.Rate.Set) >} [Sales Qty])
, MonthYear),1 )
What am I doing wrong here or does FirstSortedValue not appropriate for Measures?
My experience with set analysis and expression coding is limited. Any help would be appreciated.
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So, this is what I would do....
Sum({<MonthYear = {"$(=Date(Max(MonthYear, 1), 'MMM-YYYY'))"}>} [Sales Qty])
Sum({<MonthYear = {"$(=Date(Max(MonthYear, 2), 'MMM-YYYY'))"}>} [Sales Qty])
Sum({<MonthYear = {"$(=Date(Max(MonthYear, 3), 'MMM-YYYY'))"}>} [Sales Qty])
Now I am completely confused as to why you don't want to do this? You somehow seems to make it harder than it should be by using FirstSortedValue and than add an expression for the second argument? But why? if all you need is the Sales Qty for the Max(MonthYear), why not use set analysis?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
To get the value of last month you can tr
Sum({<MonthYear = {"$(=MAX(Total MonthYear))"}>} [Sales Qty])
OR
Sum({<MonthYear = {"$(=MAX(Total MonthYear))"}>} [Count])
Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Thanks for the response.
However, I need to be able to index the data returned that's why I'm attempting to use the "FirstSortedValue" function.
Any ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if adding rank would suit?
for example to bring the most value
sum(
aggr(
if(rank(Sum({<MonthYear = {"$(=MAX(Total MonthYear))"}>} [Sales Qty]),4)=1,Sum({<MonthYear = {"$(=MAX(Total MonthYear))"}>} [Sales Qty]))
,Dimension)
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What exactly are you trying to get as a desired output?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I'm trying to get the value for Sales Qty for each of the last three months from table in initial post. I figured i could use a similar expression for each one and index the value.
Expression 1 would result in 37502, so i tried:
=FirstSortedValue([Sales Qty],-aggr(Sum([Sales Qty]),[MonthYear]),1)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Should just be this (slightly modifying @Yoshidaqlik solution above by adding Date function and the format)
Sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} [Sales Qty])
This would work as long as MonthYear is created in the script like this
Date(MonthStart(DateField), 'MMM-YYYY') as MonthYear

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is the issue im having caused because FirstSortedValue only work with dimensions?
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, it works for expressions also, but in your case you seem to want to get the value associated with the max MonthYear.... or are we not getting it right?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
let's try using the below expression:
sum( {< [MonthYear] = {"$(=FirstSortedValue( [MonthYear], -aggr( Sum( [Sales Qty] ), [MonthYear] ), 1 ))"} >} [Sales Qty] )
for last month sales.
then replace 1 with 2 and 3 to get other months sales.

- « Previous Replies
- Next Replies »