37 Replies Latest reply: Oct 11, 2016 1:36 PM by Zili Deng Branched to a new discussion. RSS

    Selecting Arbitrary Date Ranges

    Josh Good

      Learn how to make arbitrary date range selections using a from and to date selection.

       

      http://www.youtube.com/watch?v=UTDyO68BYL8

       

        • Re: Selecting Arbitrary Date Ranges

          First of all thank you for the posted video!

          But still I have a problem with my date ranges. It seems like I can select fields on change of variables(Calendar object) using trigger

          but I can't do it in other way around... by selecting dates in the field my variables are not changing

          Why is that?

          I've attached my qvw file.

            • Re: Selecting Arbitrary Date Ranges
              Josh Good

              If found the issue.  You had a typo in your variable names for the triggers that were setting the variables when a selections was made on the date field. Under Document Properites -> Triggers  I changed the Field Event Triggers for Datums.

               

              You had the variable as: vSakuma Datums  when it needs to be vSakumaDatums  (no space)

              You also had the variable as: vBeigudatums when it needs to be vBeiguDatums (capital 'D')

               

              Hope that helps.

              Josh

            • Re: Selecting Arbitrary Date Ranges

              Thank you very much for the post.  As a newbie to QlikView I found this straightforward and to the point.  Now I wish the QV Community Search would have found it; I had to Google this toppic to find you.  The good news is it was at the top of the results.  Again, thank you.

              • Re: Selecting Arbitrary Date Ranges
                Steve Lord

                Great video, thanks!

                 

                I realized that my firstsortedvalue formulas would pick most current records in the year I selected on my listbox, then not show them if they were outside the variable date range in my inputboxes.  This did a beautiful job synchronizing inputbox and listbox and letting the chart expression pull what it's supposed to pull.  (I'm working with timestamped records, so it would be excruciating to try to manually select from thousands of timestamps in the listbox. )

                • Re: Selecting Arbitrary Date Ranges
                  Varun Prakash Paulraj

                  Great Video Thanks

                   

                   

                   

                  Regards

                  • Re: Selecting Arbitrary Date Ranges

                    Thanks pretty much for your clear explanation, It's working good!

                    • Re: Selecting Arbitrary Date Ranges
                      Damian Slanovic

                      Hi everybody.

                       

                      I followed the instructions on the video, but have problems with the EndDate. Look what happens:calendar.JPG.jpg

                       

                      "Emisión Comprobante" is like the OrderDate in the video.

                      "Selecciones Actuales" means "Current Selections".

                      I'm using calendar objects. When selecting the start date, the selection is made. When selecting the end date, nothing changes.

                       

                      What I'm doing wrong?

                       

                      Regards.

                      Damian

                      • Re: Selecting Arbitrary Date Ranges
                        Rohan Rane

                        Dear Josh,

                         

                        Thanks for the great explaination.

                         

                        However i am getting an issue in dates.qvw.

                        When i am opening from server (as a developer) the vBeiguDatums variable works correctly.

                        But when i am opening as a user from the IIS,i am not able to select the maxdate in calendar object.

                         

                        Suppose the max date is 1/12/2014,when i am selecting 1/12/2014 in the calendar object it is not letting me select,instead 1/11/2014 is getting selected.

                        Is this a bug ?

                        Can you please help me.

                         

                        Regards,

                        Rohan

                          • Re: Selecting Arbitrary Date Ranges
                            Josh Good

                            Hi Rohan,

                             

                            There are may factors that could be at play here depending on your specific qvw and the data in it.  There could be an error in the setup that is causing the wrong date to be selected or the date you are trying to select on (1/12/2014) doesn't exist in the data so QlikView is picking a the closest date less than your selection. 

                             

                            To fully help you, I would need to see the QVW.  Would you be able to post it or so a version with scrambled data?

                             

                            -Josh

                            Qlik

                              • Re: Selecting Arbitrary Date Ranges
                                Rohan Rane

                                Dear Josh,

                                 

                                Thanks for the reply.

                                However the issue was with the Qlikview setup (server version).

                                I had qlikview sr9 on my development server whereas on production,it was SR8.

                                Once upgraded to SR9,the issue no longer exists

                                One more question regarding the calendar object though,is there a way to set a blank value as defualt in calendar object ?

                                When a user opens the qlikview report,the calendar object should be blank.

                                 

                                Regards,

                                Rohan

                                  • Re: Selecting Arbitrary Date Ranges
                                    Josh Good

                                    Hi Rohan,

                                     

                                    You can set the document to remove the selections on the calendar upon open.  Look under Setting->Document Properties -> Tiggers.  Select "OnOpen" and use the "Select in Field" action.  By setting the Search String to blank, any selection will be cleared.   You may also need to add action to set the variable to blank as well.

                                     

                                    -Josh

                                    Qlik

                                     

                                     

                                      • Re: Selecting Arbitrary Date Ranges
                                        Rohan Rane

                                        Hi Josh

                                        I had already tried this way,but the issue is qlikview recovers sessions,so

                                        if a user does some selection in the calendar and then opens the app again

                                        it starts from where he left,which is not what I want.

                                        I always want the default value in calendar object to be blank.

                                        Is there a way to disable session recovery for certain apps and keeping it

                                        on for the other apps ??

                                         

                                        Regards,

                                        Rohan

                                • Re: Selecting Arbitrary Date Ranges
                                  Nadeem Shaikh

                                  Hi,

                                   

                                  I have been struggling to get my date range selections working but i dont understand where i am failing .

                                  If any body could help me with my sample app or a hint where im doing it wrong . !

                                  I would grreatly appreciate it.

                                   

                                  Regards,

                                  Nadeem

                                  • Re: Selecting Arbitrary Date Ranges
                                    venkat sai

                                    Hi Josh,

                                     

                                    How can i select arbitrary date ranges from Multibox(Year,quater,month,day)..Can you please share any qvw or video to implement...

                                     

                                    sample3.png

                                     

                                    Thanks

                                    Venkat

                                    • Re: Selecting Arbitrary Date Ranges
                                      sujeet singh

                                      AWESOME

                                      • Re: Selecting Arbitrary Date Ranges

                                        Hi Josh,

                                         

                                        Thanks a lot for the sharing. I'm recently exploring QlikView again for another project. I tried follow the steps shared in your video but still I can't get it to work. I'm stuck at the 4.05 of your video where you demo by entering some date into the input field of start date and enter it. For my case, there is no changes happen (not even at 'current selection').

                                         

                                        I'm wondering what is the issue. Here, I'm attaching the file. Hope you can advice.

                                         

                                        Thanks!

                                        • Re: Selecting Arbitrary Date Ranges
                                          R. G.

                                          HEy Josh,

                                          thank you for the video! It helps me very much.

                                           

                                          But i have an question:

                                           

                                          What is if the user set vEndDate before the vStartDate or on the other way round?

                                          i want to put something like this somewhere, but i don't know or where:

                                          if(vStartDate>vEndDate, vEndDate, vStartDate) for vStartDate

                                          and

                                          if(vEndDate<vStartDate, vStartDate, vEndDate) for vEndDate

                                           

                                          best

                                          Thanks!

                                            • Re: Selecting Arbitrary Date Ranges
                                              Josh Good

                                              Hi RG,

                                               

                                              I'm glad you find the video helpful.  I like your enhancement of adding this logic.  You would do this in the expression for the action trigger when the variable is changed (2:38 in the video Selecting Arbitrary Date Ranges - YouTube).

                                               

                                              -Josh

                                              Qlik

                                                • Re: Selecting Arbitrary Date Ranges
                                                  R. G.

                                                  hey Josh,

                                                  thank you for your answer!

                                                  i tried this solution but it dont work... maybe you have another idea?

                                                   

                                                  I have another Question:

                                                  I put three Buttons to change the Date directly for 3 month, 1 year and 2 years beginning with today.

                                                  At the buttons config i put an action (extern -> set variable)  and change the variable of vStartDate and vEndDate with value like this: "vStartDate" & "=Date(today(1)-91)" -> 3 month and "vEndDate" & "= Date(today(1))"

                                                  The ScrollerBar changes the value and also the CalendarBox. But in the diagramm it changes nothing.

                                                  the strange thing is, that it work in develope mode but in webview it doesnt work... do you know why?

                                              • Re: Selecting Arbitrary Date Ranges
                                                Ashok Vaddireddy

                                                Hi Josh,

                                                Thanks for the video. It's really awesome one.

                                                 

                                                I used the same in my project. It is working, but as per the user requirement, on document open, the dates 01-APR-2016 to today date should be selected. So I used Document Event Triggers>> On Open>> V_STARTDATE='01-APR-2016' AND V_ENDDATE=TODAY(). It is working, it is selecting the dates in ORDER_DATE field but the issue is it is not reflecting in the calendar object. (Calendar object is showing blank on document open).

                                                 

                                                Is there any way to do that? Thanks in advance.

                                                  • Re: Selecting Arbitrary Date Ranges
                                                    Josh Good

                                                    Hi Ashok,

                                                    Try adding a "Select Possible" to the end of your triggers. Sometimes that makes the selections appear as desired.

                                                     

                                                    -Josh

                                                    Qlik

                                                    • Re: Selecting Arbitrary Date Ranges
                                                      Steve Lord

                                                      Calendar object never let anyone select more than one day.  I quit using it and just use the list boxes, sort state ascending, numeric value descending.  End users requested I make the listboxes long (maybe 6 inches) to aid in selecting longer date ranges.  Would be nice if Qlik fixed the calendar object since it is a more efficient use of space if it can be used to select more than one day.   (I could use variable input boxes for date start/end, but the date formatting has to be precisely right, and input boxes were a tad finicky, so end users had an easier time just selecting 50 days on a listbox.)  At the least, you can do date(floor(date)) for your date listbox to only show whole days.

                                                       

                                                      Also a year-month listbox and certainly a year listbox to help with longer date ranges provided they include whole months or years.

                                                    • Re: Selecting Arbitrary Date Ranges
                                                      YUN BAI

                                                      Thanks Josh. This is a really helpful video.

                                                       

                                                      I have two questions. Is that possible that the Slider/Calander Object not linked to a date field (such as OrderDate in the example)? Can I have a general calendar and when I choose the StartDate and EndDate, the records in my other objects would change? If I can do it, how should I do?

                                                       

                                                      Another question is, I saw some posts shows that we need to create a master calendar in Script as below. But from your example, seems like we don't really write those code in script. Am I understand correct? Or the script and the example you showed in the video are totally different things?

                                                       

                                                      LET VarMinDate = Num(Peek('OrderDate',0,'OrdersQuery'));

                                                      LET VarMaxDate = num(peek('OrderDate', -1, 'OrdersQuery'));

                                                      LET VarToday = num(Today());

                                                       

                                                      TempCalendar:

                                                      LOAD

                                                      $(VarMinDate) +  RowNo() -1 as DateNum,

                                                      Date($(VarMinDate) +  RowNo()-1 ) as TempDate

                                                      AutoGenerate

                                                      $(VarMaxDate) - $(VarMinDate);

                                                       

                                                       

                                                      MasterCalendar:

                                                      LOAD

                                                      TempDate as OrderDate,

                                                      Week(TempDate) as Week,

                                                      Year (TempDate) as Year,

                                                      Month(TempDate) as Month,

                                                      Day(TempDate) as Day,

                                                      WeekDay(TempDate) as WeekDay

                                                      Resident TempCalendar;

                                                       

                                                      Thanks,

                                                      Becky

                                                      • Re: Selecting Arbitrary Date Ranges
                                                        Natalia Chinchilla

                                                        Hi dear,

                                                         

                                                        I have a field "YearMonth" as value = "2016-06" and I need to apply same functionality as is shown in this video, but does not work in my lab. When I set StartDate = "2016-04" dont apply in filters: in List box, the table and current sellecion filters.

                                                         

                                                        I'm wondering if this functionality works in dates with this format YYYY-MM?

                                                         

                                                        Thanks,

                                                        • Re: Selecting Arbitrary Date Ranges
                                                          Zili Deng

                                                          Thanks Josh. This is a really helpful video.

                                                          I tried to follow your step to develop a date selection app but I cannot make the end date work.

                                                          Please see attached sample. The End Date can not select the date.

                                                           

                                                          Please help.

                                                           

                                                          Thank you very much in advance!

                                                            • Re: Selecting Arbitrary Date Ranges
                                                              Josh Good

                                                              Hi Zili,

                                                               

                                                              I took a quick look at your app. For me, End Date is making selections however I'm not getting the expected selections.  It appears to be selecting up to the day before the date selected.  I believe this is because your dates are not actually date-time.  When you load your data you need to use the floor function.  So it will be something like this:

                                                               

                                                              date(floor(BAVIM_Date_Mod), 'MM/DD/YYYY') as BAVIM_Date_Mod

                                                               

                                                              -Josh

                                                              Qlik

                                                            • Re: Selecting Arbitrary Date Ranges
                                                              Zili Deng

                                                              Hello Josh,

                                                               

                                                              Your solution rocks. It works very well.

                                                               

                                                              Thank you very much! You are awesome!