I would like to compare the current sales with previous year. I understand I have problems because I would like to match the current specific date with date of the previous year.
In my script, I create a calendar with two fields, the current date and the date with previous year. After I do left join with the table with current sales and currrent date, after I do left join with the previous sales and previous date.
I think the left joins aren't fine but I don't know how I can do it.
I show you the script:
Date(FECHA) AS FECHA, Date(AddMonths(FECHA,-12)) AS FECHA_PREV, ;
Load Date(MinDate + IterNo() -1 ) AS FECHA While (MinDate + IterNo() - 1) <= Num(MaxDate);
Min(FECHA) AS MinDate,
Max(FECHA) AS MaxDate
Load NUMERO_CONTRAT, FECHA, sum(SALES) as SALES_CURRENT
Resident ORIGEN group by NUMERO_CONTRAT, FECHA;
Load NUMERO_CONTRAT, FECHA AS FECHA_PREV, sum(SALES) as SALES_PREV