FirstSortedValue of previous year

[jeanful]

Hello there,

I have the following script

MYTAB_T:
LOAD * INLINE [
Product, Date, Value
BIKE, 05-01-2024, 100
BIKE, 06-01-2024, 200
BIKE, 07-01-2024, 70
JACKET, 06-01-2024, 10
JACKET, 09-01-2024, 20
JACKET, 10-01-2024, 50
BIKE, 02-01-2025, 1000
BIKE, 03-01-2025, 2000
BIKE, 04-01-2025, 3000
BIKE, 05-01-2025, 990
JACKET, 04-01-2025, 100
JACKET, 05-01-2025, 200
JACKET, 08-01-2025, 500
];

MYTAB:
NoConcatenate
LOAD [Product], Date(Date#([Date], 'MM-dd-yyyy'), 'MM-dd-yyyy') AS [Date], [Value]
RESIDENT MYTAB_T;

DROP TABLE MYTAB_T;

I then built a table in the sheet with: Product, Year(Date) and two measures:

- FirstSortedValue( [Value], [Date] )
- FirstSortedValue( [Value], -[Date] )

These work properly, e.g. BIKE | 2025 | 1000| 990.

I would add a new measure to calcolate the last Value for the previous year.
E.g. I'd have BIKE | 2025 | 1000| 990 | 70 (where 70 is the last value of BIKE 2024). How can I achieve this?

Thanks!!

[marcus_sommer]

Remove the calculated dimension of Year(Date) - year and other period-information should be in general native fields within the data-model and not derived in the UI - from the table and apply 4 expressions like:

FirstSortedValue({< Year = {2025}>} [Value], [Date])
FirstSortedValue({< Year = {2025}>} [Value], -[Date])
FirstSortedValue({< Year = {2024}>} [Value], [Date])
FirstSortedValue({< Year = {2024}>} [Value], -[Date])

[jeanful]

Thank you! The year must be present in the table, it's a requirement.
Furthermore, the year can be selected by means of a filter and the solution should retrieve dynamically the previous year (avoiding harcoding like 2025 and 2024).

thank you!

[marcus_sommer]

It mustn't be static, for example with:

... {< Year = {"$(=max(Year))"}>} ...
... {< Year = {"$(=max(Year)-1)"}>} ...

and the max() could be also used to label the expression appropriate.

[jeanful]

I see, thank you. But if I have the year as a column in the reporting table **bleep** doesn't work. It seems like  a conflict between the column Year = 2025 and the calculation Year-1 (e.g.2024) at the same time, and it returns nothing...

[marcus_sommer · Accepted solution]

Yes, it doesn't work in this way because the 2024 data don't belong to the 2025 and vice versa - which is also completely independent to any selection state respectively set analysis condition.

Therefore my above suggestion as the usually most pragmatic way to create such views. Of course there are other ways, for example:

• using interrecord-functions like above() or before() which give access to any visible cell of the chart
• adjusting/extending the data-model to appropriate dimensions - maybe with: The As-Of Table - Qlik Community - 1466130
• enforcing values against their dimensionalities - applying {1} and TOTAL nearly each value could be assigned to any dimension-value (not recommended)