topic Re: How to show data from last year in a table in QlikView

How to show data from last year in a table

nicalbert — Tue, 22 Sep 2020 15:40:11 GMT

Hello everyone,

I want to compare yesterday's data with data from the same date last year.

I'm using the following to get yesterday's data, and it works as expected:

=count({<[Call Date]={'>=$(=DayStart(today()-1))<$(=DayStart(today()))'}>} DISTINCT [Call Number])

I do however have problems getting the "same date last year" part to work. I have looked at many threads in this forum, and ended with this approach, which does not work:

=count({<[Call Date]={'>=$(=Date(Addyears(DayStart(today()-1),-1))<$(=Date(Addyears(DayStart(today()),-1))'}>} DISTINCT [Call Number])

Can someone give me help me with this?

Thanks,

Nic

Re: How to show data from last year in a table

sunny_talwar — Tue, 22 Sep 2020 17:01:39 GMT

Try this

=Count({<[Call Date] = {">=$(=DayStart(AddYears(Today()-1, -1))) < $(=DayStart(AddYears(Today(), -1)))"}>} DISTINCT [Call Number])

Re: How to show data from last year in a table

nicalbert — Tue, 22 Sep 2020 17:42:08 GMT

Thank you! I just tried it, it does return a result, but the result is not accurate. But you've put me on the right track!

I looks as if it was capturing a year of worth of data (from a year ago until yesterday) instead of just "yesterday last year".

I tried moving the parenthesis around, but no luck (i either end up with 0 or a number representing the complete data)...

Re: How to show data from last year in a table

sunny_talwar — Tue, 22 Sep 2020 17:46:14 GMT

can you try this

=Count({<[Call Date] = {">=$(=DayStart(AddYears(Today()-1, -1)))<$(=DayStart(AddYears(Today(), -1)))"}>} DISTINCT [Call Number])

Re: How to show data from last year in a table

nicalbert — Tue, 22 Sep 2020 17:56:12 GMT

I tried, it gives the same result. And it seems to be the exact same expression (might be wrong!).

Re: How to show data from last year in a table

neelamsaroha157 — Tue, 22 Sep 2020 18:18:14 GMT

=Count({$<[Call Date]={'$(=Date(Addyears(DayStart(today()))','$(=-1)))'}>}Distinct [Call Number])

Re: How to show data from last year in a table

nicalbert — Tue, 22 Sep 2020 18:24:12 GMT

Thanks! This one returns 0...

Re: How to show data from last year in a table

sunny_talwar — Tue, 22 Sep 2020 18:46:04 GMT

Removed space before and after < in the set analysis

Re: How to show data from last year in a table

nicalbert — Wed, 23 Sep 2020 12:41:18 GMT

Retried this morning, it worked right away!?!?!?!?!

thanks for you valuable help!

Re: How to show data from last year in a table

sunny_talwar — Wed, 23 Sep 2020 12:45:32 GMT

Awesome!!