Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
CC_Abbott
Contributor
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.

20 Replies
CC_Abbott
Contributor
Contributor
Author

Yes, in order for the expression to work I have to label the measure as Moving Avg.