10 Replies Latest reply: Jul 10, 2012 5:14 PM by David Nelson RSS

    Stale bookmarks?

      I'm using a bookmark (BM08) where this expression has been entered into a search box:

       

      =dev_finish_date>now()-3

       

      Then I have a gauge chart that is using this expression:

       

      =count({BM08} request_id)

       

      The problem is that it pulls the correct figures for a while but as time goes by the number doesn't change. Is there an expression I can use that's dynamic?

        • Re: Stale bookmarks?

          I'm looking into set analysis but still don't seem to be getting the right syntax for what I need:

           

          Count({1<dev_finish_date={">now()-3"}>}request_id)

           

          Count({< dev_finish_date = {">=$(=Date(now() - 3, 'M/D/YYYY'))"} > } request_id)

           

          Neither of these are displaying any data, I don't even get a needle. Help?

            • Re: Stale bookmarks?

              may be this:

               

              Count({1<dev_finish_date={">date(now()-3)"}>}request_id)

               

              Regards,

              Kiran ROkkam.

                • Re: Stale bookmarks?

                  No that doesn't work, either... If it's any help these are my set statements in case the date format is what's causing it:

                   

                  SET ThousandSep=',';

                  SET DecimalSep='.';

                  SET MoneyThousandSep=',';

                  SET MoneyDecimalSep='.';

                  SET MoneyFormat='$#,##0.00;($#,##0.00)';

                  SET TimeFormat='h:mm:ss TT';

                  SET DateFormat='M/D/YYYY';

                  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                  SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

                    • Re: Stale bookmarks?

                      For further clarification, I'm trying to get the count of all the "request_id" records (a unique value) that have a "dev_finish_date" between the current time (now) and 72 hours/3 days ago.

                      • Re: Stale bookmarks?

                        The expression assumes that Qlikview recognies as dev_finish_date as a date field. You can check that tags for fields in table viewer. If its not date field format as date field in the script using date# function.

                         

                        In the previous expression use timestamp() function instead of date. I didnt notice the timestamp part.

                         

                        Kiran.

                          • Re: Stale bookmarks?

                            These didn't work either, with the date/time formats:

                             

                            Count({1<dev_finish_date={">date(now()-3), M/D/YYYY"}>}request_id)

                             

                            Count({1<dev_finish_date={">date(now()-3), M/D/YYYY, h:mm:ss TT"}>}request_id)

                              • Re: Stale bookmarks?

                                Can you try this (noticed some syntax issues):

                                 

                                Count({1<dev_finish_date={">$(=Timestamp(now()-3))"}>} request_id))

                                 

                                 

                                We need to try and match the dev_finish_date format to the return we have in the set analysis string.

                                 

                                Kiran Rokkam.

                                  • Re: Stale bookmarks?

                                    Getting closer - that at least gives me a value. But it is too high, quite a bit off. Ideas?

                                      • Re: Stale bookmarks?

                                        I am not aware of the data but the following are possible reasons:

                                        1. If you data with request_id in the future (> now) the expression evaluates them since there is upper limit
                                        2. Notice using {1} in the set analysis. It ignores all the selections (expect for >now()-3) resulting in more values.

                                         

                                        Kiran Rokkam.

                                          • Re: Stale bookmarks?

                                            Much better. This expression is working for now:

                                             

                                            Count({$<dev_finish_date={">$(=Timestamp(now()-3))"}>} request_id)

                                             

                                            It's only a tad off now, and unless you have any other suggestions for the expression I'll have to wait and see if the server refresh rate is to blame for the discrpeancy.

                                             

                                             

                                            EDIT:

                                            I think it may be a server refresh issue, so this might be the most accurate they'll get. Thank you for all your time.

                                             

                                            Message was edited by: bikeking8