-
Re: Counting yesterday values in QV
Nicole Smith Jan 10, 2018 6:10 PM (in response to Nicolas Albert )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
Nicolas Albert Jan 10, 2018 4:09 PM (in response to Nicole Smith )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 Jan 10, 2018 5:21 PM (in response to Nicolas Albert )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
Nicolas Albert Jan 11, 2018 8:39 AM (in response to Nicole Smith )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 Jan 11, 2018 11:22 AM (in response to Nicolas Albert )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 Jan 11, 2018 11:29 AM (in response to 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.
-
example.qvw 149.2 K
-
Re: Counting yesterday values in QV
Nicolas Albert Jan 11, 2018 2:13 PM (in response to Nicole Smith )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.
-
community.qvw 157.0 K
-
Re: Counting yesterday values in QV
Nicole Smith Jan 11, 2018 5:33 PM (in response to Nicolas Albert )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)
-
community.qvw 154.8 K
-
Re: Counting yesterday values in QV
Nicolas Albert Jan 12, 2018 9:35 AM (in response to Nicole Smith )YES!
This is the right one! Thank you so very much for you time. It's really appreciated.
Nic
-
-
-
-
-
-
-
-