20 Replies Latest reply: Nov 1, 2010 12:53 PM by Matt Cayford RSS

    Basic date input query

      Hi all,

       

      I have just started playing around with the Personal Edition connecting to an ODBC database and have a basic query on allowing the end-user to search a table based on dates.

      For example, I have a table (Accidents) which contains Home & Date fields. I would like the user to be able to see how many Accidents each Home had in a given week (Monday-Sunday). Within the Edit Script command, I can use commands (i.e. where Accidate >= 40469 and Acciddate <= 40475) etc) but the intended end-user would not have access to Edit Script .

      After searching on here, I have tried various options such as using the slider/calendar object and entering two Excel tables [startdate] and [enddate] but cannot make it work.

      Would appreciate some basic advice please.

       

      Thanks,

       

      Matt

        • Basic date input query
          Lee Alderdice

          You could try, the Calendar Generation script in this qvw file attached.

          Replace the below on line 48 with Accidate

          Calendar

           



          AS [PUTINYOURDATEFIELD],

          Connect up the tables that are on the Main tab to the Accidate field you have created above. Include Accidate in the Charts you are working with.

          You can then select by Month, Day, Quarter, Year etc. Just update the script to generate whatever else it is that you want.

          • Basic date input query
            Miguel Angel Baeyens de Arce

            Hello Matt,

            Say you have something similar to the following as data and calendar:

             

            Accidents:LOAD * INLINE [PersonID, Date, HomeA, 22/04/2010, City1B, 21/04/2010, City1C, 26/04/2010, City2D, 22/04/2010, City2E, 23/04/2010, City1F, 21/04/2010, City2G, 26/04/2010, City1H, 22/04/2010, City1A, 23/04/2010, City1F, 21/04/2010, City2]; MinMaxDates:LOAD Min(Date) AS MinDate, Max(Date) AS MaxDateRESIDENT Accidents; LET vMinDate = Peek('MinDate', -1, 'MinMaxDates');LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDates'); DROP TABLE MinMaxDates; TempCalendar:LOAD Date(Num($(vMinDate)) + IterNo()) AS TempDateAUTOGENERATE 1 WHILE Date(Num($(vMinDate)) + IterNo()) <= Date($(vMaxDate)); Calendar:LOAD TempDate AS Date, Month(TempDate) AS Month, WeekDay(TempDate) AS WeekDayRESIDENT TempCalendar; DROP TABLE TempCalendar;


            You may use this file objects as a base to check records between two dates.

            Hope that helps.

                • Basic date input query

                  Thanks Lee, Miguel but unfortunately it seems I cannot open any other Qlikview files created by anyone else(I am using the free Personal Edition currently)

                  Thanks Hopkinsc - I have looked at that already and created two excel spreadsheets [startdate} and [enddate] with 1 column of dates in each and created the 2 calendar options which list the dates from the spreadsheets but the part I am struggling to understand is this :

                   

                  " Create two variables 'vStartDate' and 'vEndDate' with the following definitions:

                  vStartDate = GetFieldSelections([Start Date])
                  vEndDate = GetFieldSelections([End Date]) "

                   

                  If I rightclick on one of the calendars , the variable option is blank and I cannot see how to edit?

                   

                  Thanks again all.


                  Matt

                    • Basic date input query
                      Chris Hopkins

                      You will first need to create the variables :

                      Document Properties >> Variables tab

                      Once the 2 variables are created, you should then be able to select them from the calender properties

                       

                      • Basic date input query
                        Chris Hopkins

                        Hi Cayfmatt,

                        I have just looked at how i did my start / end dates. I only used 1 Date table in the script (TradingDate) I have attached a sample, the sample has no data in it but you will be able to see my expressions and variables.

                        First create the variables - vStartDate, vEndDate

                        look at Document Properties>> Triggers>> Variable Event Triggers. I have actions assigned to my variables (To be completely honest, im not exactly sure what these actions i do, all i know is that my dates work :)

                        Look at the properties of the two calenders, there is a min and max expression on each.

                        lastly i have an expression on the 'Transaction Header' box which basiacally only shows information for the date range selected.

                        I hope this helps....

                        • Basic date input query
                          Chris Hopkins

                          http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/5707.Expression.JPGHi again Matt,

                          Hope this helps, here are some screen shots of exprssions etc i have used.

                          Create Variables >> Doc Properties>> Variables

                          Variables Triggers (on input) >> Doc Properties>>Triggers>>Variables Triggers

                          Calander Prop>> Calender Properties>> copy whats in image on both Min Vlaue, Max Value (Obviously change the expression to match the field your entering it in. i.e. =Min(TradingDate) goes in Min Value, Max(tradingDate) goes in Max Value. do the same for both calenders.

                          Expression >> goes in your main chart/box which displays the information.

                          Obviosly you need to change TradingDate to match your date field.

                          http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/1452.Create-Variables.JPG

                           

                           

                           

                            • Basic date input query

                              Thanks hopkinsc, your help is much appreciated.

                              I have set up two Calendars, [startdate] which is set to variable vStartdate and [enddate] set to variable vEnddate with both set to min/max acciddate. I am now trying to understand the expression stage.

                              • Basic date input query
                                Chris Hopkins

                                Ok, like i said, the trigger expressions on the variables i dont understand, maybe someone else here can tell us what it does exactly?

                                =if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

                                The expression that is added to your results box should just display results which fall within your chosen date range.

                                 

                                  • Basic date input query
                                    Chris Hopkins

                                    Remember to make sure that you change my examples to match your variable names EXACTLY as it is case sensitive...

                                      • Basic date input query

                                        hopkinsc - i am confused as to the two separate Expressions that you displayed above:

                                        Where should the following expression be entered?

                                        =if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

                                        The last expression you highlighted (the Sum({<Trading Date one - am I correct in thinking this should be in the Edit Expression field on the table that displays the result to the end user?

                                        I am still struggling with the expression I would like for my particular count: ie. count(acciddate) where the accidents recorded between vStartdate and vEnddate. I have tried adapting your Sum expression for my Count to no avail.


                                        Thanks again

                                          • Basic date input query
                                            Chris Hopkins

                                            Hi, that expression goes as a variable trigger.

                                            Document Properties>>Triggers>> variable Event triggers. select your variable, click on add actions (on input), click add, select in field, and enter:

                                            date in the field box

                                            and the expression in the string box.

                                            do this for both variables..

                                              • Basic date input query

                                                Thanks Hopkinsc, it looks like everything is fine up to the expression stage in my final box that the user sees. I keep getting error in expression when I am trying to enter the count accident formula between the two variable dates - any ideas? I'll keep trying.

                                                  • Basic date input query
                                                    Chris Hopkins

                                                    Hi, what is the expreesion you are writing?

                                                      • Basic date input query

                                                        Hi hopkinsc,

                                                         

                                                        I have been trying variations on the following (as previously mentioned, I am very new to this so still finding my way around!) but the best I can do is just return the total number.

                                                        COUNT ({< acciddate = {">=$(=Date(vStartdate))"}*{"<=$(=Date(vEnddate))"} >}acciddate)

                                                        Basically, I just want to count every accident in a given time period as chosen by the user using the 2 calendar options [startdate] & [enddate}, connected to variables vStartdate & vEnddate respectively.

                                                         

                                                        Thanks


                                                        Matt

                                                         

                                                         

                                                          • Basic date input query
                                                            Chris Hopkins

                                                            Hi Matt,

                                                            Could you post a sample so i can see your tables names and field names??

                                                              • Basic date input query

                                                                The table I am looking at is called accidents which contains a field acciddate which is just a date field that an accident occured (and another field which gives me the Home name from another table but that is fine)

                                                                I wish to extract the number of accidents that each Home has recorded by counting how many times the acciddate appears.

                                                                I have a calendar object [startdate] which is linked to a variable vStartdate . Also have another calendar object [enddate] which is linked to vEnddate. Both variables have the following search string:

                                                                =if(vStartdate > 0,'>=' & date(vStartdate, '$(DateFormat)')) & if(vEnddate >0,'<=' & date(vEnddate,'$(DateFormat)'))

                                                                I have a chart which I wish to display the total number of accidents for each Home in a given period by the user using the two calendar objects to give a startdate and enddate and its the expression in this chart which I am struggling with to 'connect' to the two calendars (I am writing the expression for this count in Chart Properties/Expressions/Definition.

                                                                Previously, I have just used count(acciddate) to give a total for all Accidents by Home which worked fine but now I wish for the user to select variable dates using the calendars.

                                                                Thanks again.


                                                                Matt