FirstSortedValue from an Aggr Measure

[CC_Abbott]

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.

[Yoshidaqlik]

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

[CC_Abbott]

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]

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

[sunny_talwar]

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

[CC_Abbott]

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]

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]

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.