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.

[CC_Abbott]

Hi Sunny,

Really appreciate the help.

I'm trying to get each of the last 3 months sales.  That is why I thought I could use the FirstSortedValue function with aggr so that the expression could be indexed.

Thanks

[CC_Abbott]

Hi Agigliotti,

Thanks for the suggestion.  However the expression returns a 0 value no matter the index.

[sunny_talwar]

3 months sales in a single text box or 3 text boxes?

[CC_Abbott]

3 different boxes.  I could use the same expression with index into different KPIs.

[sunny_talwar · Accepted solution]

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?

[CC_Abbott]

Thanks Sunny.

Here's some progress.

=Sum({<MonthYear = {"$(=Date(Max(MonthYear, 1), 'MMM-YYYY'))"}>} [Sales Qty]) 

The expression above works fine as long as certain selections are not made in my Filter Pane.  Filter selections that affect my Count (see below) cause my sales qty to change.  Im assuming my filters are affecting my date range and thus limiting my sales data.

No Filters

KPI

Interesting that =Sum({<MonthYear = {"$(=Date(Max(MonthYear, 1), 'MMM-YYYY'))"}>} [Count]) always works as expected.

I can create a variable for sales that uses an ignore list.  Can i implement an ignore into the Sales expression below?

=Sum({<MonthYear = {"$(=Date(Max(MonthYear, 1), 'MMM-YYYY'))"}>} [Sales Qty]) 

Here is my variable used for sales.

v_Sales.Qty = Num(Sum( {< $(v.Ignore_Compl.List) >} [Sales Qty]), $(v.Format.Num))

[CC_Abbott]

Ok Finally got the solution;

Heres the final expression:
=Sum({<$(v.Ignore_Compl.List), MonthYear = {"$(=Date(Max(MonthYear, 1), 'MMM-YYYY'))"}>} [Sales Qty])

[sunny_talwar]

What selection causes your expression to not work? do you want this number to not change based on any selection what so ever? try this

=Sum({1<MonthYear = {"$(=Date(Max({1} MonthYear, 1), 'MMM-YYYY'))"}>} [Sales Qty]) 

[CC_Abbott]

Thanks for the response.  All is well now with that expression. I appreciate the help.  I have another challenge.

I have an expression that generates a weighted moving average.  It uses the Above function to give weight to the new value.

Entry Month\Year	Weighted Moving Avg	Rate	Sales Qty	Count
Jan-2019	0.011414323	0.00978605	41692	408
Feb-2019	0.013231815	0.017472631	27586	482
Mar-2019	0.012379257	0.010389955	45236	470
Apr-2019	0.012017082	0.011172007	56928	636
May-2019	0.012120369	0.012361371	40125	496
Jun-2019	0.012606576	0.01374106	35514	488
Jul-2019	0.011816444	0.009972801	37502	374
Aug-2019	0.011871918	0.012001357	32413	389

For the initial value, we have separate rule since it does not have a prior data point to include in the weighted moving average.  

Here is the complete expression

If(Rowno() = 1, //First one, so use Control Average
$(v.Lambda.Num) * $(v_Count.Cnt) / $(v_Sales.Qty) + (1-$(v.Lambda.Num)) * $(v_ControlPeriodRate)
, //ELSE, use previous Moving Avg
$(v.Lambda.Num) * $(v_Sales_Count.Rate) + (1-$(v.Lambda.Num)) * Above([Moving Avg])) //Label must be Moving Avg

Each one of the variables uses MonthYear.

Any ideas on how I could get the index the last three values from the section underlined above?

[sunny_talwar]

Within Above([Moving Avg]), Moving Avg is the expression label?