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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.