topic Re: Counting yesterday values in QV in QlikView

Counting yesterday values in QV

nicalbert — Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I'm working on a simple dashboard and I'm having troubles trying to count the numbers of items that were created "yesterday". I'm presenting the overall count of items and need to display "yesterday's" count as well. It would give something like:

492 items made in total
13 made yesterday

As you would imagine, "yesterday" needs to be a dynamic value based on today's date.

I did try many ways, but they all end up with a zero value or an error in the expression. I'm using the following items:

ItemCreatedDate, format is YYYY-MM-DD
ItemId

Can anyone help me on that?

Regards,

Nic

Re: Counting yesterday values in QV

Nicole-Smith — Wed, 10 Jan 2018 20:53:59 GMT

I think something like this should work:

count({<ItemCreatedDate = {'$(=date(today()-1))'}>} ItemId)

If the above doesn't work, you may have a timestamp that you can't see attached to the ItemCreatedDate. You can get rid of this timestamp in your load script by using floor().

Re: Counting yesterday values in QV

nicalbert — Wed, 10 Jan 2018 21:05:38 GMT

Thanks Nicole,

Just tried it and it returns "0" as a value.

You mentioned timestamp.. the actual field is formatted "YYYY-MM-DD hh:mm:ss", should I specify that in the expression? Is is better to get rid of the hh:mm:ss portion?

Nic

Re: Counting yesterday values in QV

Nicole-Smith — Wed, 10 Jan 2018 22:21:20 GMT

If you have timestamps, you could format it as just a date in the script, or you can use >= and < in your set analysis to say where it is between yesterday and today like so:

count({<ItemCreatedDate = {'>=$(=today()-1)<$(=today())'}>} ItemId)

This will handle the timestamps in the calculation.

Re: Counting yesterday values in QV

nicalbert — Thu, 11 Jan 2018 13:39:51 GMT

Once again, thank you Nicole!

I tried the set analysis this morning without any luck. I'm still having "0' as a result, although this is not the right answer.

Could you give an advice on how to load the timestamp as a date in the script?

Nic

Re: Counting yesterday values in QV

Nicole-Smith — Thu, 11 Jan 2018 16:22:00 GMT

The second one should definitely work (I've tested it with some sample data myself). Can you upload an example file with your data? It's hard to tell why it isn't working without seeing the document you're working from. Here is a good document on uploading an example file to the community: Preparing examples for Upload - Reduction and Data Scrambling

Re: Counting yesterday values in QV

Nicole-Smith — Thu, 11 Jan 2018 16:29:27 GMT

In addition, attached is the file I used to test my expression. Can you check in your file if QV is recognizing the field as a timestamp? If it isn't, that's the problem, and you need to use timestamp#() in your load script in order to let QV know it is one.

Re: Counting yesterday values in QV

nicalbert — Thu, 11 Jan 2018 19:12:05 GMT

Just tried yours, which works. But my file won't work! I honestly don't get where this fails...

And I looked at the field and it is recognized as a timestamp by QV.

Attached is an example of the data that I use to work with.

Thanks again for your help.

Re: Counting yesterday values in QV

Nicole-Smith — Thu, 11 Jan 2018 22:33:05 GMT

It looks like we need a DayStart() in order for this to work with your data (your sample file attached with this change):

count({<ItemCreatedDate = {'>=$(=DayStart(today()-1))<$(=DayStart(today()))'}>} ItemID)

Re: Counting yesterday values in QV

nicalbert — Fri, 12 Jan 2018 14:35:35 GMT

YES!

This is the right one! Thank you so very much for you time. It's really appreciated.

Nic