Announcements
cancel
Showing results for
Did you mean:
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
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)

Specialist II

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

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)

4 Replies
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)

Specialist II

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

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)

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.
Contributor
Author

Thank you so much !

Community Browser