INTERVALMATCH AND YOY COMPARISON

[Alan_Tilney]

I have two tables.

Sales and Management

SALES:
LOAD * INLINE [
SALESDATE, CUSTOMERID, QUANTITY, MODEL, REVENUE, ALLOCTYPE
1/2/2020, A1, 1, MODA, 100, DYNAMIC
1/4/2020, A1, 1, MODA, 200, STATIC
1/7/2020, A1, 2, MODB, 300, STATIC
1/3/2019, A1, 5, MODC, 1000, STATIC
1/3/2020, A2, 1, MODA, 500, DYNAMIC
1/6/2019, A2, 4, MODB, 600, DYNAMIC
1/4/2020, A2, 10, MODC, 2000, STATIC
1/5/2020, A3, 50, MODA, 5000, STATIC
];

MANAGEMENT:
LOAD * INLINE [
MANAGER, CUSTOMERID, ACCOUNTNAME, STARTDATE, ENDDATE
ALAN, A1, A1INC, 1/1/2020, 30/5/2020
FRED, A1, A1INC, 1/6/2020, 31/12/2020
ALAN, A2, A2INC, 1/1/2020, 30/5/2020
MARK, A3, A3INC, 1/1/2020, 31/12/2020
];

I use INTERVALMATCH to join the two tables on SALESDATE and CUSTOMERID, which then lets me show the data in a pivot table.  This shows MANAGER, CUSTOMERID, ACCOUNTNAME, STARTDATE, ENDDATE and REVENUE (During the management period).

// USING INTERVALMATCH AND KEY
JOIN (SALES)
INTERVALMATCH (SALESDATE, CUSTOMERID)
LOAD STARTDATE, ENDDATE, CUSTOMERID
RESIDENT MANAGEMENT;

OR

//USING INTERVAL MATCH AND LEFT JOIN
FINAL_TABLE:
INTERVALMATCH (SALESDATE) LEFT JOIN LOAD STARTDATE, ENDDATE RESIDENT MANAGEMENT;
LEFT JOIN (SALES) LOAD DISTINCT * RESIDENT MANAGEMENT;
DROP TABLE MANAGEMENT;

This works fine.

I would like to show YOY performance, to see if the manager has increased sales compared to the year before.

I think I can just reload the MANAGEMENT table, subtract a year from the STARTDATE, ENDDATE and INTERVALMATCH/JOIN the result again.  Is this the right way?  It seems a bit long winded and in my "real" data I have > 100,000 sales records.

I have attached and example QVW.

[Marcos_Ferreira_dos_Santos]

Hi Dknyalan,

It seems that the YOY analysis in your case need only a Set Analysis over the SALES table (fields SALESDATE, CUSTOMERID and REVENUE), doesn't it?

If so you should not necessarily create 'fake' records in the MANAGEMENT table...