Compare last year Sales

[lomi89]

Hi Team,

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:

PRIMAS_ACTUALES:
LOAD

Date(FECHA) AS FECHA,
Date(AddMonths(FECHA,-12)) AS FECHA_PREV,
;

Load Date(MinDate + IterNo() -1 ) AS FECHA While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(FECHA) AS MinDate,

Max(FECHA) AS MaxDate

RESIDENT ORIGEN;

LEFT JOIN

Load
NUMERO_CONTRAT,
FECHA,
sum(SALES) as SALES_CURRENT

Resident ORIGEN
group by
NUMERO_CONTRAT,
FECHA;

LEFT JOIN

Load
NUMERO_CONTRAT,
FECHA AS FECHA_PREV,
sum(SALES) as SALES_PREV

Resident ORIGEN
group by
NUMERO_CONTRAT,
FECHA;



DROP TABLE ORIGEN;

[Ksrinivasan]

Hi,

no need to complicate,

Table1:

load

FECHA as Date,

Sales

from load file

create table

DIM = Year(FECHA)

Measure=Sum(Sales)

table as blow

ksrinivasan

[lomi89]

Thank you ksrinivasan but I didn't explain very well. I would need a table for getting this graph:

 

 

The problem is when I check the current sales in a specific month with previous sales (really the same) , the figures are a little different.

[Ksrinivasan]

hi,

yes you can create bar chart with this data what we get from script.

instead of year (FECHA) 

Date([FECHA],'MMM-YYYY') as Month_Year,

then you can call in chart as

DIM=Month_Year

Measure=Sum(Sales)

chart will be your way,

ksrinivasan