topic Re: Getting Last year and current year data in QlikView

Getting Last year and current year data

Fion_c — Mon, 27 Jul 2020 04:40:00 GMT

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

tresB — Mon, 27 Jul 2020 05:18:05 GMT

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 — Mon, 27 Jul 2020 05:18:54 GMT

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)

Brett_Bleess — Tue, 11 Aug 2020 17:07:06 GMT

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

Regards,
Brett

Fion_c — Wed, 12 Aug 2020 06:43:52 GMT

Thank you so much !