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.
