Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yes, in order for the expression to work I have to label the measure as Moving Avg.