Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Fion_c
Contributor
Contributor

Getting Last year and current year data

How can I sum the sales amount for 2019 and 2020 respectively.
I have data as follows:

client    sales     date of transaction
a              100          11/2/2019
b              200          2/3/2019
c              120          7/7/2019
d              300          16/1/2020
e              800          31/8/2020
f               1000      12/12/2020

2 Solutions

Accepted Solutions
tresesco
MVP
MVP

Ideally you should have a Year field as well created in the script, like:

Year([date of transaction]) as Year

Then you could simple use expression like: Sum({<Year={2019}>}sales) and Sum({<Year={2020}>}sales)

In case, you don't have year field you can try like:

Sum({<[date of transaction]={"=Year([date of transaction])=2019"}>}sales), and

Sum({<[date of transaction]={"=Year([date of transaction])=2020"}>}sales)

Or, to make it more dynamic (w.r.t. current year and previous year)

Sum({<[date of transaction]={"=Year([date of transaction])=Year(Today())-1"}>}sales), and

Sum({<[date of transaction]={"=Year([date of transaction])=Year(Today())"}>}sales)

 

 

View solution in original post

zhadrakas
Specialist II
Specialist II

most of the time you create a time table in your data model.

check this also https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

Then you have a column year which you can use as dimension and set an expression on sum(Sales).

here are samples for standalone expressions if you need it.

Sum({$<year={2019}>}sales)
Sum({$<year={'$(=$(vLastYear))'}>}sales)

 

View solution in original post

4 Replies
tresesco
MVP
MVP

Ideally you should have a Year field as well created in the script, like:

Year([date of transaction]) as Year

Then you could simple use expression like: Sum({<Year={2019}>}sales) and Sum({<Year={2020}>}sales)

In case, you don't have year field you can try like:

Sum({<[date of transaction]={"=Year([date of transaction])=2019"}>}sales), and

Sum({<[date of transaction]={"=Year([date of transaction])=2020"}>}sales)

Or, to make it more dynamic (w.r.t. current year and previous year)

Sum({<[date of transaction]={"=Year([date of transaction])=Year(Today())-1"}>}sales), and

Sum({<[date of transaction]={"=Year([date of transaction])=Year(Today())"}>}sales)

 

 

zhadrakas
Specialist II
Specialist II

most of the time you create a time table in your data model.

check this also https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

Then you have a column year which you can use as dimension and set an expression on sum(Sales).

here are samples for standalone expressions if you need it.

Sum({$<year={2019}>}sales)
Sum({$<year={'$(=$(vLastYear))'}>}sales)

 

Brett_Bleess
Former Employee
Former Employee

Just adding a Design Blog post as well that may be of some use to others that see this thread:

https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Fion_c
Contributor
Contributor
Author

Thank you so much !