# 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
Did you mean:
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.

Labels (8)

• ### Sum conditional

1 Solution

Accepted Solutions
MVP

Try this by creating two new fields in the script

``````Sales:
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)``

2 Replies
MVP

Try this by creating two new fields in the script

``````Sales:
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)``

Contributor II
Author

thanks a lot. It works!!! 😄