Need help with set analys

[Tugge1]

Hi,

I have similar data like this:

Temp:
Load* inline
[Date,Product,Fakt
20251001,1, 1
20251015,1, 7
20250810,2, 5
20250814,2, 9
20241014,1, 3
20241003,1, 2
20240807,2, 4
20240821,2, 1
];

Temp2:
Load
Date,
Product,
Fakt,
Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD') as Regdate

Resident Temp;

Drop Table Temp;

Master:
Load
Date,
Product,
Fakt,
Regdate,
Num(Year(Regdate) * 100 + Month(Regdate)) as Period,
Num(((Year(Regdate)-1)*100) + Month(Regdate))as PeriodLY

Resident Temp2;

Drop Table Temp2;

If a user select "Period"(one or more) then I need a set analysis to return the sum of "Fakt" for each product for the same period last year.
This need to work in a straight table in a chart.
Can someone please help me with that? Best regards/Patric

[stevedark]

Hi @Tugge1 

The first thing I note from the script is that you can do away with the temporary table by using a preceding load. The Temp2 part can sit on top of the first load like this:

Load
Date,
Product,
Fakt,
Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD') as Regdate
inline
[Date,Product,Fakt
20251001,1, 1
20251015,1, 7
20250810,2, 5
20250814,2, 9
20241014,1, 3
20241003,1, 2
20240807,2, 4
20240821,2, 1
];

That's got nothing to do with set analysis, but it could make your load scripts more performant and clearer to read.

If you want to compare just the latest month with the corresponding previous you would have something like this for the latest month:

sum({<Regdate={'$(=date(max(Regdate),'YYYY-MM-DD'))"}>}Fakt)

The expression for the same period prior month would be:

sum({<Regdate={'$(=date(addmonths(max(Regdate),-12),'YYYY-MM-DD'))"}>}Fakt)

It's always worth testing set analysis expressions like that by putting them into a table with Regdate as a dimension, you should then see that the first will only have a value on the 2025-10-01 row and the second on the 2024-10-01 row.

The other thing I would suggest is changing the date format for the period to be MMM-YYYY, as you don't need to see the 1st on each period. This format needs to match exactly in both the load script and set analysis.

I've done a blog post, with an example you can download, on prior period analysis here:

https://www.quickintelligence.co.uk/prior-period-comparison/

Hope that helps,

Steve