Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jaimeeduardo_gc
Contributor II
Contributor II

Sum sales same days different year

Hi everybody, i have this data that represents my sales for different dates from last year and this year:

Date

Id_store

Sales

2019-07-01

1

50

2019-07-02

1

10

2019-07-03

1

25

2019-07-04

1

23

2019-07-05

1

457

2019-07-06

1

42

2020-07-02

1

20

2020-07-04

1

100

2020-07-06

1

38

2019-07-01

2

23

2019-07-02

2

423

2019-07-03

2

65

2019-07-04

2

8765

2019-07-05

2

34

2019-07-06

2

123

2019-07-07

2

346

2019-07-08

2

54

2019-07-09

2

45

2019-07-10

2

54

2020-07-01

2

200

2020-07-11

2

167

2019-07-01

3

234

2020-07-13

3

643

I need to sum only sales of last year in the same store, same days and same month of  i had sales this year, something like this:

Id_store

Sales 2019

Sales 2020

1

75

158

2

23

367

3

0

643

*CURRENT YEAR OR SELECTED YEAR IS THE MANDATORY

For example:

in id_store =  1 and year 2019 only sum sales of days: 2, 4 and 6 because in the current year (2020) I just have sales these days.

in id_store = 2 and year 2019 only sum sales of day: 1 because is the only day with sales in 2020 that i had sales in 2019.

in id_store= 3 and year 2019 there is no days equals to current year so the sum of sales in 2019 is 0 but in 2020 is 643.

I´m trying using P() function but i'm not getting the expected result.

 

I attach an example of what i'm trying to do.

 

I would be very grateful if someone could help me or suggest another type of solution. Thank you very much everyone for the help. 

1 Solution

Accepted Solutions
sunny_talwar

Try this by creating two new fields in the script

Sales:
LOAD Date,
	 year(Date) as year,
	 day(Date) as day,
	 num(month(Date)) as month,
	 id_store,
	 sales,
	 id_store&'|'&Num(Date) as CurrentYearKey,
	 id_store&'|'&Num(AddYears(Date, -1)) as PreviousYearKey;
LOAD * INLINE [
    Date, id_store, sales
    2019-07-01, 1, 50
    2019-07-02, 1, 10
    2019-07-03, 1, 25
    2019-07-04, 1, 23
    2019-07-05, 1, 457
    2019-07-06, 1, 42
    2020-07-02, 1, 20
    2020-07-04, 1, 100
    2020-07-06, 1, 38
    2019-07-01, 2, 23
    2019-07-02, 2, 423
    2019-07-03, 2, 65
    2019-07-04, 2, 8765
    2019-07-05, 2, 34
    2019-07-06, 2, 123
    2019-07-07, 2, 346
    2019-07-08, 2, 54
    2019-07-09, 2, 45
    2019-07-10, 2, 54
    2020-07-01, 2, 200
    2020-07-11, 2, 167
    2020-07-01, 3, 234
    2020-07-13, 3, 643
];

and then use this expression for Last Year

Sum({<CurrentYearKey = p(PreviousYearKey), year>} sales)

image.png

View solution in original post

2 Replies
sunny_talwar

Try this by creating two new fields in the script

Sales:
LOAD Date,
	 year(Date) as year,
	 day(Date) as day,
	 num(month(Date)) as month,
	 id_store,
	 sales,
	 id_store&'|'&Num(Date) as CurrentYearKey,
	 id_store&'|'&Num(AddYears(Date, -1)) as PreviousYearKey;
LOAD * INLINE [
    Date, id_store, sales
    2019-07-01, 1, 50
    2019-07-02, 1, 10
    2019-07-03, 1, 25
    2019-07-04, 1, 23
    2019-07-05, 1, 457
    2019-07-06, 1, 42
    2020-07-02, 1, 20
    2020-07-04, 1, 100
    2020-07-06, 1, 38
    2019-07-01, 2, 23
    2019-07-02, 2, 423
    2019-07-03, 2, 65
    2019-07-04, 2, 8765
    2019-07-05, 2, 34
    2019-07-06, 2, 123
    2019-07-07, 2, 346
    2019-07-08, 2, 54
    2019-07-09, 2, 45
    2019-07-10, 2, 54
    2020-07-01, 2, 200
    2020-07-11, 2, 167
    2020-07-01, 3, 234
    2020-07-13, 3, 643
];

and then use this expression for Last Year

Sum({<CurrentYearKey = p(PreviousYearKey), year>} sales)

image.png

View solution in original post

jaimeeduardo_gc
Contributor II
Contributor II
Author

thanks a lot. It works!!! 😄