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:




      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({< 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:





              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.



                          • 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.




                                            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