Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)
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)
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
Thank you so much !