Calculated Month-Year sales of last year in a Straight table.

[AN2024]

I need to calculate as measure the sales of the last year based in the following straight table:

The field Period is defined in the script as a date, I tried to calculate the column Sales Last Year with this formula, but I don´t understand why the result in the mentioned column as 0.

Sum({<Period = {"$(=Date(AddYears(Period, -1), 'MMM-YYYY'))"}>} Sales)

Straight table:

Period	Sales	Sales Last Year (Expected output)
Ene-2020	15
Feb-2020	20
Mar-2020	8
Abr-2020	15
May-2020	16
Jun-2020	80
Jul-2020	58
Ago-2020	67
Sep-2020	76
Oct-2020	85
Nov-2020	94
Dic-2020	103
Ene-2021	113	15
Feb-2021	122	20
Mar-2021	131	8
Abr-2021	140	15
May-2021	149	16
Jun-2021	158	80
Jul-2021	167	58
Ago-2021	177	67
Sep-2021	186	76
Oct-2021	195	85
Nov-2021	204	94
Dic-2021	213	103

[rubenmarin]

Hi, set analysis is calculated before the table to filter the data, so it doesn't uses the value of each row.

There are different solutions for this, to name some:

- Use Above(Sum(Sales), 12) to retrieve the value of 12 rows above the one that uses this expression

- Use an AsOf table: https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

- Add additional rows to create the SalesLY Field, loading the same data but adding a year to Period and create the new column as Sum(SalesLY)

[AN2024 · Accepted solution]

@rubenmarin Thank you for your helps it works.