Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
CC_Abbott
Contributor

FirstSortedValue from an Aggr Measure

Hello,

I have the following data in Qlik Sense.

DimensionMeasures
Entry Month\YearRateSales QtyCount
Jan-20190.001080414169245
Feb-20190.0013544162758655
Mar-20190.0011404164523629
Apr-20190.001109215692859
May-20190.0010605594012538
Jun-20190.0010549363551537
Jul-20190.0009864423750231

 

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.

1 Solution

Accepted Solutions
sunny_talwar

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?

View solution in original post

20 Replies
Yoshidaqlik
Creator II

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

YoshidaQlik https://www.youtube.com/channel/UC1I9P8MqCZEhB6Nw3FdSqng
CC_Abbott
Contributor
Author

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?

 

Yoshidaqlik
Creator II

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

 

YoshidaQlik https://www.youtube.com/channel/UC1I9P8MqCZEhB6Nw3FdSqng
sunny_talwar

What exactly are you trying to get as a desired output?

CC_Abbott
Contributor
Author

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)

 

sunny_talwar

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
CC_Abbott
Contributor
Author

Is the issue im having caused because FirstSortedValue only work with dimensions?

 

Thanks

sunny_talwar

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?

agigliotti
Partner - Champion

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.