Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
lomi89
Contributor III
Contributor III

Compare last year Sales

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;

3 Replies
Ksrinivasan
Specialist
Specialist

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_0-1611116142631.png

ksrinivasan

lomi89
Contributor III
Contributor III
Author

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

example.PNG

example table.PNG

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
Specialist
Specialist

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