9 Replies Latest reply: Jan 12, 2018 9:35 AM by Nicolas Albert

Counting yesterday values in QV

Nicolas Albert Jan 10, 2018 3:42 PM

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 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().
Like Show 0 Likes (0)

Actions
- 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
  
  Like Show 0 Likes (0)
  
  Actions
  - 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.
    
    Like Show 1 Like (1)
    
    Actions
    - 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
      
      Like Show 0 Likes (0)
      
      Actions
      - 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
        
        Like Show 0 Likes (0)
        
        Actions
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        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
        
        Like Show 1 Like (1)
        
        Actions
        
        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
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post