topic Re: Sum sales same days different year in QlikView

Sum sales same days different year

jaimeeduardo_gc — Sat, 16 Nov 2024 00:12:10 GMT

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.

Re: Sum sales same days different year

sunny_talwar — Mon, 03 Aug 2020 19:34:54 GMT

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)

Re: Sum sales same days different year

jaimeeduardo_gc — Tue, 04 Aug 2020 19:39:33 GMT

thanks a lot. It works!!! 😄