Counting yesterday values in QV

[nicalbert]

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

[Nicole-Smith]

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().

[nicalbert]

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

[Nicole-Smith]

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.

[nicalbert]

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

[Nicole-Smith]

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

[Nicole-Smith]

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.

[nicalbert]

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.

[Nicole-Smith · Accepted solution]

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)

[nicalbert]

YES!

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

Nic