8 Replies Latest reply: Feb 26, 2012 10:03 AM by David Nelson RSS

    Count - if - date in chart expression for rolling week?

      I have two fields, dev_finish_date and request_id.

       

      I need to create a gauge chart counting all the request_id's with a dev_finish_date occurring in the past seven days (rolling). Another field, app_ id has to be included in the calculation, too, as a required selection. I've racked my brain and the best syntax I can come up with for the gauge chart's expression is this:

       

      count({<dev_finish_date={[>now-7]}, app_id={'22'}>} request_id
      
      

       

      but that obviously isn't working.

       

      Any ideas?

        • Count - if - date in chart expression for rolling week?
          Stefan Wühl

          Try

           

          count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id

            • Re: Count - if - date in chart expression for rolling week?

              I get "No data to display" for some reason. As long as it can count the number of records in the "request_id" field, I'm fine with that. I can just apply a bookmark upon sheet load for the "app_id" field. This doesn't work, though:

               

              count({<dev_finish_date={'Today-7'}>} request_id)


              or this:

               

               

              count({<dev_finish_date={">$(=date(today()-7))"}>} request_id


              ...and I still have three more date calculations to put into graph form by Tuesday. After someone helps me sort this out, can anyone tell me what language Qlikview is written in so I can pick up a reference book?

                • Count - if - date in chart expression for rolling week?
                  Stefan Wühl

                  I assume it's just a format issue. How is your dev_finish_date formatted? and what is your default DateFormat (set in the script)?

                  You could also give it a try to remove the date() function from the set expression search expression:

                   

                  count({<dev_finish_date={">$(today()-7)"}, app_id={'22'}>} request_id

                  • Re: Count - if - date in chart expression for rolling week?

                    Through the use of bookmarks (again), I found a way to permanently detach the results of my gauge chart. I made a bookmark with the required search conditions via a search box:

                     

                    =dev_finish_date>now()-7

                     

                    ...along with the app_id "22" selected. For the chart I used this expression:

                     

                    =Count({BM08} request_id)

                    Done. Now, no matter what bookmarks or records the user clicks on, the desired calculation will show. Thanks for all the help.

                      • Re: Count - if - date in chart expression for rolling week?

                        For some reason, the bookmarked search is "stale" every time I load/reload the QVD, For example, when I loaded it today and checked the results of the bookmarked search, it pulled up dates from 2/21-2/23 as opposed to 2/26-2/24. The expression I'm using is:

                         

                        =Count({BM08} request_id)

                         

                         

                         

                        ...where the bookmark is a result of the search where app_id "22" is manually selected and the following string is entered into the search box:

                         

                        =dev_finish_date>now()-3

                         

                        The criteria has changed to the past 3 days, hence the 3. I've also tried "today" in place of "now", but the same thing happens. I have the document reload on open via module operation, so it shouldn't be a data lag issue. In answer to the above question concerning date format, the dates are formatted as 2/26/2012 10:41:34 AM; using a 24 hour clock (13:00, 14:00, etc).

                         

                        The two identical expressions that were posted to try out:

                         

                        count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id

                         

                        only give me a "no data to display" on the gauge chart. I see there's a parantheses missing, but I can't figure out where to put it. Please help?

                          • Re: Count - if - date in chart expression for rolling week?

                            Just a thought; would the bookmarked search behave as required if there was a dollar sign somewhere in there? If so, what would be the correct search string?

                             

                            Just tried this as an expression, as well:

                             

                            =count({<dev_finish_date={">$(=date(today()-3))"},app_id={'22'}>} request_id)

                             

                            but this gave me a result of 0 on the gauge chart.

                            • Count - if - date in chart expression for rolling week?
                              Stefan Wühl

                              The missing parenthesis is needed at the very end of the line, closing the count() function arguments.

                              =count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id)

                               

                              Be careful using now() function, the standard timer mode here is 1, so you will constanty update the time and function (could slow down the system). Check the different timer modes in the Help for detail.

                               

                              I am not sure what you mean with the bookmarked search and the dollar sign, could you post your expression here?

                               

                              Your dev_finish dates are actually timestamps, so just make sure to format the search expression accordingly, try  these:

                               

                              count({<dev_finish_date={">$(=timestamp(today()-7),'M/DD/YYYY hh:mm:ss TT')"}, app_id={'22'}>} request_id)

                               

                              count({<dev_finish_date={">$(=timestamp(today()-7))"}, app_id={'22'}>} request_id)

                               

                              count({<dev_finish_date={">$(=today()-7)"}, app_id={'22'}>} request_id)


                               

                              Have you tried the expressions also in a text box? Just had a thought that it might be a gauge chart issue.

                               

                              Could you upload a small sample file here to the forum?

                               

                              Regards,

                              Stefan

                                • Re: Count - if - date in chart expression for rolling week?

                                  Thanks. The suggestion you provided:

                                   

                                  count({<dev_finish_date={">$(=timestamp(today()-7))"}, app_id={'22'}>} request_id)

                                   

                                  works for now when used as the expression in the gauge chart. I'll see if it's consistent come tomorrow afternoon. That's the problem with working with date fields, I've discovered; you might have to wait a day or two to get another "data sample" when it comes to consistency checks.