topic Re: FirstSortedValue of previous year in App Development

FirstSortedValue of previous year

jeanful — Wed, 24 Sep 2025 10:01:01 GMT

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!!

Re: FirstSortedValue of previous year

marcus_sommer — Wed, 24 Sep 2025 11:53:43 GMT

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])

Re: FirstSortedValue of previous year

jeanful — Wed, 24 Sep 2025 11:58:01 GMT

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!

Re: FirstSortedValue of previous year

marcus_sommer — Wed, 24 Sep 2025 12:03:59 GMT

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.

Re: FirstSortedValue of previous year

jeanful — Wed, 24 Sep 2025 12:06:16 GMT

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...

Re: FirstSortedValue of previous year

marcus_sommer — Wed, 24 Sep 2025 13:28:06 GMT

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)