Use Last Year field value of dimension in table expression

[Nicholas_Wessels]

Hi Qlik Community

I'm hoping you can provide some assistance with the below problem.

I am trying to build an expression in a Qliksense table that sets a field to it's corresponding last year field value.

The chart below requires a [Season Code] dimension and 2 measures, [In Transit Qty] and [LY In Transit Qty] (simple sums). I've added the [LY Season Code] dimension for added context.

The desired outcome for Season Code S20's (Summer 2020) [LY In Transit Qty] must be S19's [In Transit Qty].

The LY expression currently looks as follows:

This works fine if only one season is selected as seen below: 

However when no Season Code is selected the p([LY Season Code]) in the set analysis amounts to all the possible LY Season Codes, which is not desired. As I want the Season Code to be set to it's corresponding LY Season Code for each row in the table.

I don't think a RangeSum sollution would suite as the chart should be sortable in multiple ways as well as including winter seasons.

Thank you for any feedback in advance and please let me know if you require any further information

Kind Regards

Nicholas

[nsm1234567 · Accepted solution]

As you end up with more and more complex variations of this I'd eventually suggest you look into building an "AsOf" table.  For your narrow use-case, does something like the below expression maybe work?

=sum(aggr(Above(Sum([In Trnst Qty]),1),([Numeric Weight],(NUMERIC))))

I've attached this in a QVW.  I know this is a Sense query, but the principle should be the same.  You'll see I've set the sort order in the QVW to be random and it still gives the correct answer.  The key here is the inclusion of the numeric weight for your season which isn't inherently numeric like the date would be ( I guess you could sort using the date also).  As long as the numeric weight is correct, adding in the winter seasons should still work (I haven't tested yet though).

[Nicholas_Wessels]

Thank you Nsm1234567, this is working for my current use case.

I've added clearing the [Style Season Code] in the set analysis to allow you to select seasons and still produce the correct results, as seen below:

=sum(aggr(Above(Sum({<[Style Season Code]=>}[In Trnst Qty]),1),([Numeric Weight],(NUMERIC))))