34 Replies Latest reply: Dec 6, 2010 7:34 AM by Dan-Ketil Jakobsen RSS

    Check for date intervals

    Dan-Ketil Jakobsen

      Hi all

       

      I have only been workng with qlickview for little over a month, so bear with me if my question seem stupid or misplaced.

      I have a table that lists all customers and orders that are recuring. That means that a customer can place an order for say item 100 and ask for delivery every monday until a stop order is issued.

      The table would look like

      customer, order, item, qty, day, FromDate, ToDate

      1000,000123,100,4.0,1,20101101,9999999

      1000,000322,234,5.0,1,20101101,20101231

       

      My users want to select a week, say 201049. The report should then show all he customers, item, qty for each day that week.

      How do I get this to work. I guess I need to create a variable that I can use to search between the two dates.

      If it is easier to use two seperate calenders, from and to,in order to select the period, then that is just fine.

      If the ToDate is set to 9999999 it will be translated to fromdate + 2 years.(allready taken care of in the datamarts.

       

      Hope anyone can help me with this

       

      Best regards

      Dan

        • Check for date intervals

          Hello Dan,

          there is more then one step you can take. Lets start with a first solution. This would be to convert your incoming date columns into QV date fields. For this use at script level:

          Load . . . date(FromDate, 'YYYYMMDD') AS FromDate; . . .
          . In the same load script you should establish the data-periods you need ie with
          Load . . . Month(date(FromDate, 'YYYYMMDD')) AS FromDateMonth; . . .
          . The same you can do with your ToDate. Then you and your Users can select a FromDateMonth (or of course a week) directly in a list box and will indirect select all according dates (FromDate) to it.

          The better way, in a next step, is to create your own calender (a calender for each date-field you need). There are many threads and post concerning this topic.

          HtH for the first approach

          Roland

            • Check for date intervals
              Dan-Ketil Jakobsen

              Thanks Roland for quick response. :)


              I forgot to mention that I allready have the date in both integer and true date format in load. Sorry.

              Still I am not sure how this helps me. Please forgive me if I have misread your reply.

              Look at one line of my base table

              customer, order, item, qty, day, FromDate, ToDate

              1000,000123,100,4.0,1,20101101,9999999

              (Fromdate/todate is true dateformat)

              If my user now ask for any customer with recuring order for year 2010 week 49 the above line should apear.
              If I use the date field in the spesific line It would be year 2010, week 1.
              It should recognize the above line and place it in week 49, since the selected period is between the from and to date.

              Best regards
              Dan

                • Check for date intervals

                  Hello Dan,

                  now I know a litte bit more. But this I could have known earlier if I had read your first post more carefully, sorry!

                  Your users want to specify a week and then all rows should appear in your (straight) table with the selected week between fromdate and todate. For this purpose I would use a variable event trigger. Let me introduce this proceeding with a date value instead of a week. With a week value it works of course as well. First you define a variable. Next you define a Variable Event Trigger (settings --> document prop --> trigger --> Button "OnInput") with two selection actions. First action selects the according selection for the FromDate (with a searchstring like

                  ='<=' & vDate & ' >=01.01.2010' ).
                  Second action defines the selection for the ToDate. When using an input box for your variable the trigger should fire (= execute the actions) and your two dates (and the according rows) should be selected.

                  HtH

                  Roland

                    • Check for date intervals
                      Dan-Ketil Jakobsen

                      Hi Roland

                      Trying to understand what you want me to do. I will post some screendumps so you can see what I am working on.

                      I cant get the inputbox to do anything at all, so obviously I am doing somethng terrinble worng. But what?

                      Main desktop:

                      Variable settings:

                      onInput: ='<=' & vStartDate & ' >=01.01.2010' )

                       

                      Inputbox:

                       

                      Thank you for your patience.

                       

                      Dan

                        • Check for date intervals

                          Hi Dan,

                          this looks great. But I am afraid you are using an older version. I build a little example with V9SR6. Would you look for your version (--> help --> about QV). Perhaps it is possible to update.

                          RR

                            • Check for date intervals
                              Dan-Ketil Jakobsen

                              Version 8.50.6206.5

                               

                              Dan

                              • Check for date intervals
                                Dan-Ketil Jakobsen

                                I feel I am so close...

                                I got the variable vStartDate to interact with the data.

                                Below you will find my desktop. Now I have included the straight table that shows all current test data.

                                My main obejct is the pivot chart.

                                When I enter a date in the StatDate input field the pivot chart alters.

                                But it only do a = test. I still fight to get it to check for intervals.

                                My measurement expression

                                Sum (IF(vStartDate >= [From Line Date] ,[Ordered quantity - basic U/M],0))

                                My idea is that the user enters both start and end date and then the appropriate fields show.

                                I.E start 04.10.2010, end 11.10.2010, should show the summary for the 5 first rows. Two customers.

                                 

                                Dan

                                  • Check for date intervals
                                    Dan-Ketil Jakobsen

                                    Knowing that it was a long shot in the dark, I also tried to alter my expresion to

                                    sum(If(vStartDate>=[From Line Date] and vEndDate <= [To Line Date],[Ordered quantity - basic U/M],0))

                                    Needless to say it did not help at all.

                                    Embarrassed

                                    Dan

                                      • Check for date intervals

                                        Hello Dan,

                                        I put an SET Analysis Term to support you:

                                         

                                        sum({ <FromDate= { "$(= '>=01.01.' & Year(vDate))" }, ToDate= { "$(= '>=' & vDate & ' <23.03.2011')" } >} Value)


                                        As far as I remember SET Analysis was introduced in 8.5. (the Triggers above did come later). You should be able to use the term above in your expression. Note that the value of the second date "23.03.2011" is for my testing only. Change it to a proper val i.e. "999999".

                                        One hint: In V9 (I am currently using a stable version QV9SR6) and in the brandnew V10 there are a lot of new features. Think about an update!

                                        RR

                                          • Check for date intervals
                                            Dan-Ketil Jakobsen

                                            Thanks Roland will try this one.

                                             

                                            Regarding update, I just got the word that we are set to upgrade tomorrow. Version 9.

                                             

                                            Dan

                                            • Check for date intervals
                                              Vidyut Verma

                                              Hi,

                                               

                                              Instead of going Set Analysis way, You could create another table (linked to the Orders table) which has Week, Product, Order, Quantity details for all the coming years. This could be done by using Interval Match on dates in New table, using the Order table as the FromDate-ToDate Map.

                                              Vidyut

                                              • Check for date intervals
                                                Dan-Ketil Jakobsen

                                                Tried it, but I only get 0.00 values in return.

                                                Set analysis is completely new to me so I will have to read up abit on that part.

                                                But here is my expresion.

                                                sum({ <[Line Date, From]= { "$(= '>=01.01.' & Year(vStartDate))" }, [Line Date, To]= { "$(= '>=' & vEndDate & ' <12.31.2011')" } >} [Ordered quantity - basic U/M])

                                                Maybe you would be so kind and walk me thru the code so I understand what happends?

                                                 

                                                Dan

                                                  • Check for date intervals

                                                    Hello Dan,

                                                    you are allright. I was searching for a while but now with success. I am using ONE datevalue vDate in my expression and you are using two dates. Replace your vEndDate with your vStartDate and it will work (hopefully ;-)).

                                                    SET in two words: in an expression you can use SET analysis, a mighty tool but sometimes a little bit cryptic. Look at

                                                    sum( {< MyYear= {'2010'}> } Value)

                                                    This means the well known sum. In the outer {} there is the SET-Formula. The <> include your exceptions from users choice. Here regardsless which MyYear is selected, in this expression is only '2010' "counting". SET fomulares can be combined in many ways and nested from dusk till dawn. Good Luck !!

                                                     

                                                    Hint: To see whats happening in your expression leave (while testing) the label of your expression empty. This helps sometimes.

                                                    RR

                                                      • Check for date intervals
                                                        Dan-Ketil Jakobsen

                                                        Your tip about leaving the expression name blank was useful.. Now I see more what your example return in code statement

                                                        Your code:

                                                        sum({ <[From Line Date]= { "$(= '>=01.01.' & Year(vStartDate))" }, [To Line Date]= { "$(= '>=' & vStartDate & ' <31.12.2011')" } >} [Ordered quantity - basic U/M])

                                                        Result code:

                                                        Event when I changed my StartDate input it returned the same code, and value 0.00

                                                        I therefor changed the expression to :

                                                        sum({ <[From Line Date]= { "$(= '>=' & vStartDate)" }, [To Line Date]= { "$(= '<=' & vEndDate)" } >} [Ordered quantity - basic U/M])

                                                        with following result code:

                                                         

                                                          • Check for date intervals
                                                            Dan-Ketil Jakobsen

                                                            argg, hit the post button to early..

                                                            The result from my post above:

                                                            Still I get the value 0.00 but now at least I can see that what ever a user supply as input results in changing the expresssion.

                                                            (by the way the norwegian date format is day.month.year) Smile

                                                            As you see from the below sniblet I am getting closer, but still that final touch is missing.

                                                             

                                                              • Check for date intervals

                                                                Hi Dan,

                                                                now I am a little bit confused. You told me in a former post ....

                                                                1000,000123,100,4.0,1,20101101,9999999

                                                                (Fromdate/todate is true dateformat)

                                                                If my user now ask for any customer with recuring order for year 2010 week 49 the above line should apear.
                                                                If I use the date field in the spesific line It would be year 2010, week 1.
                                                                It should recognize the above line and place it in week 49, since the selected period is between the from and to date.

                                                                ....

                                                                Because you wrote the date "20101101" ... "would be year 2010, week 1" I read it unfortunately as "01.01.2010". Sorry, this wasn't carefulls but I derived that you want your daterange to start from the beginning of the year of your startdate (now you know the reason for the '01.01." & year(vDate) in my expr.). This was obviously wrong.

                                                                As I know you now want to see all the rows where the selected week is between the from and todate.

                                                                 

                                                                To your latest post:

                                                                The syntax is correct (Yes!!) because your straight table shows valid values in the column (ok, they are all 0, not what you expect) One question: Shouldn't you consider the rows with '9999999' as a legal Enddate. This would be similar to that: ... [To Line Date]= { "$(= '<=' & vEndDate), '9999999' "} ....

                                                                To shorten the whole thing, can you post a little exam application with one or two exam selected From(To)dates and the results which you are expecting?

                                                                BTW: I like the norwegian date (and of course the norwegian people).

                                                                RR

                                                              • Check for date intervals
                                                                Swapnil Joshi

                                                                I have gone through the complete thread. Somewhere Dan mentioned Users want to search the data on the basis of week.Why dont you extract one week column with the week function in your load script and map it to list box as a filter criterion.Now sure whether I am on same page.

                                                                As I tried once week filter and rest values i.e. summation of say orders and all will be calculated by Qlikview on the fly if you set the expressions correctly.

                                                                --Swapnil

                                                                 

                                                                  • Check for date intervals
                                                                    Dan-Ketil Jakobsen

                                                                    Morning Swapsjosh

                                                                    This sounds good, but I am afraid I do not fully understand how this works.

                                                                    I have two datefileds, from and to.

                                                                    A user can supply any given week and ith that week lies betwwen these dates then the line is taken into account.

                                                                     

                                                                    Dan

                                                                     

                                                                      • Check for date intervals
                                                                        Dan-Ketil Jakobsen

                                                                        In pure desperation I even tried an if elseif statement, no luck of course.

                                                                        It yelds results, but it does not take into account my selections,

                                                                        Sum(if(vStartDate>=[From Line Date],[Ordered quantity - basic U/M],if(vEndDate<=[To Line Date],[Ordered quantity - basic U/M],0)))

                                                                         

                                                                        Dan

                                                                          • Check for date intervals
                                                                            Dan-Ketil Jakobsen

                                                                            Hi again

                                                                            I am out travelng to day, and while midair I relaised that my if statement actually works as it is.

                                                                            I have been testing with startdate 04.10.2010, end date 10.10.2010. In my if statement I say that I want startdate to be >= fromdate and enddate <= todate. In my case I would then hit.

                                                                            Apparently I will need to use a week input to make this work.

                                                                            If you look at one of my former posts you will se my an object with my entire dataset you wil find that if I type 201040 I should only get fem records.

                                                                            (If i set a week to be 201040, this equals 04.10.2010 to 10.10.2010.)

                                                                            So in short terms I need to enter a week, this have to translate to a start and end date.

                                                                            Then I need to check if my fromdate is between this range.

                                                                            But how to accomplish this i beyond my at present time

                                                                             

                                                                            Dan

                                                                             

                                                                             

                                                                              • Check for date intervals
                                                                                Dan-Ketil Jakobsen

                                                                                Roland

                                                                                We have now updated to version 9

                                                                                 

                                                                                Dan

                                                                                  • Check for date intervals

                                                                                    Hello Dan,

                                                                                    NOW is the time. After some missunderstandings I can present your adapted application.

                                                                                    I did some things: first I created a calender for selection purpose. See details in the script tab two. Note that I did a partial load (remove the "add" before loading). The whole thing is quick (and dirty) I am sure you can refine it. With this calender you do not need any variable, only a listbox. For testing purpose my "listbox" is a tablebox to show WeekLastDay and WeekFirstDay as well.

                                                                                    Second take a look into the expressions. It will take you only some seconds to understand. Note that your datefields are in reality timestamps. I would prefer to convert them into dates while loading. It's a very easier handling unless you need the timevalues. Otherwise load all dates as dates.

                                                                                    Lessons learned:

                                                                                    I should have ask earlier for an exam application. Communication is hard enough between people in their own language but even harder in a foreign language.

                                                                                    RR

                                                                                     

                                                                                      • Check for date intervals
                                                                                        Dan-Ketil Jakobsen

                                                                                        Hi Roland

                                                                                         

                                                                                        Thanks for you help, I wil look into this soulition.

                                                                                        Regarding timestamp, I do not need that, need only dates.

                                                                                         

                                                                                        Language is much more a barrier then real borders are.

                                                                                        Next time I will post an exam first thing.

                                                                                         

                                                                                        Dan

                                                                                        • Check for date intervals
                                                                                          Dan-Ketil Jakobsen

                                                                                          Thanks Roland for your input.

                                                                                          I will try to alter it so I can ask for year/week in your listbox.

                                                                                          If I can figure that one out, then I can send it for testing.

                                                                                          I tried to remove the add, actually I tried to remove both both I see no difference in the listbox.

                                                                                          Dan

                                                                                            • Check for date intervals

                                                                                              Hello Dan,

                                                                                              'add' in load script is used for partitial loading. This is the only way for me to add data to your application without loosing your data.

                                                                                              To show year and week in the listbox you can code like this:

                                                                                               

                                                                                              load . . .year(date(today() + rowno()-150)) & '-' & week(date(today() + rowno()-150)) as SelWeek];


                                                                                              RR

                                                                                               

                                                                                                • Check for date intervals
                                                                                                  Dan-Ketil Jakobsen

                                                                                                  hey, that works well.

                                                                                                   

                                                                                                  Now I have a few more questions if you feel to reply.

                                                                                                  1: When I remove add from code I get an error, normal?

                                                                                                  2: Even with both add statemenst I get an error saying "Table not found. Drop table statement" Why?

                                                                                                  3: How can I get my year-week listbox to also include years to come? Now it contains this years setup, but I would lke it also to show 2011? I will then have year in one list box and week in another.

                                                                                                  Allmost forgott your expression. I think I understand it but to be on the safe side:

                                                                                                  sum({ <[From Line Date]= { "$(= '>=' & SelWeekFirstDay &' 00:00:00' & '<' & SelWeekLastDay &' 00:00:00')" } >} [Ordered quantity - basic U/M])

                                                                                                  means

                                                                                                  evalute [From Line Date] if it is greather then or equal to SelWweekFirstDay and less then SelWeekLastDay then SUM(order qty), right?

                                                                                                   

                                                                                                  Much appriciate all your input. I have learned more about QW these few days then my entire last month I think.

                                                                                                  Dan

                                                                                                    • Check for date intervals

                                                                                                      Hi Dan,

                                                                                                      1. - 2. :I used the "add"-keyword to be able to ADD data to your data. To create the calendertables and to add to your data. If you now are reloading in a normal manner you do not need any add-keyword. I forgot to mention: then you do not need the two "drop table....." anymore. See Online-Help for details.

                                                                                                      3. For testing purpose I used the autogenerate() - function which generates automatically new rows according to the load - statement. If you want more rows (dates) in the table "SelDateTbl" then simple increase the parameter. At the moment it is 150, set it to 300 to create 300 rows.

                                                                                                      4. right. Don't forget to remove the & ' 00:00:00' when you replaced your timestamps with dates

                                                                                                      You are wellcome.

                                                                                                      RR

                                                                                                       

                                                                                                        • Check for date intervals
                                                                                                          Dan-Ketil Jakobsen

                                                                                                          Roaland, I now got it to work more or less as I pictured it. Thanks for your help.

                                                                                                           

                                                                                                          One last question, so far, How can I get the year listbox to expand til 2011,2012. I only get 2009, 2010 now. I would like it to open for looking into the future.

                                                                                                           

                                                                                                          Dan

                                                                                                            • Check for date intervals

                                                                                                              Hi Dan,

                                                                                                              glad to help. I am learning in such a process, too.

                                                                                                              For increasing your Selection-Year increase the parameter of the autogenerate() - function in the load statement. As I mentioned in my last post i.e. to 300 iterations or to get dates from 2012 to 600.

                                                                                                               

                                                                                                              year(date(today() + rowno()-150)) & '-' & week(date(today() + rowno()-150)) as SelWeek
                                                                                                              autogenerate(600);


                                                                                                              RR

                                                                                                               

                                                                                                               

                                                                                                                • Check for date intervals
                                                                                                                  Dan-Ketil Jakobsen

                                                                                                                  Thanks, works like a charm.

                                                                                                                   

                                                                                                                  I have had a talk with the group that will be using this report.

                                                                                                                  We have a from and to date for each line. But in real life they only use from date, and that is how I have created the report.

                                                                                                                  However, it is possible to se a to date, a stop date.

                                                                                                                  Attached to this reply you will find my latest version of the report. In this dataset you will find that a valid to date have been set for item 215.

                                                                                                                  If I choose any week after 01.01.2011 this item shoud not be included in the report.

                                                                                                                   

                                                                                                                  I tried to create an expression with both from and to date, but no luck of course.

                                                                                                                  I`ll bet you know how to do this.

                                                                                                                   

                                                                                                                  Dan

                                                                                                                  • Check for date intervals

                                                                                                                    Hi Dan,

                                                                                                                    the test-expression of your pivot wasn't too bad. I changed to things, one syntax error and one semantic error. So explore and try my expression that I tested in your "Table Content"-chart, of course without a label ;-)

                                                                                                                     

                                                                                                                    sum({ <[From Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" }, [To Line Date]= { "$(= '>' & SelWeekLastDay &' 00:00:00')" } >} [Ordered quantity - basic U/M])
                                                                                                                    //sum({ <[From Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" },<[To Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" } >} [Ordered quantity - basic U/M])


                                                                                                                     

                                                                                                                    Regards, Roland

                                                                                                                      • Check for date intervals
                                                                                                                        Dan-Ketil Jakobsen

                                                                                                                        Thanks, I should have seen that syntax error I guess.

                                                                                                                        Now at least I can publish this first step, and start exploring the step I know will come.

                                                                                                                        My current data cotains records for recuring orders, as well known now. We also have data, in anoter table, with stop ordrers.

                                                                                                                        This first step takes into account that one order can last for an infinite time of period. However, during this infinite period you will have periods where the customer wants to stop his delivery. i.e. christmas week.

                                                                                                                        Say it is a school that wants a recuring order, but during christmas, school is closed, so for this limited time period they issue a stop request.

                                                                                                                        Techincally we now have a table with a customer, stop date from, stop date to.

                                                                                                                        I am sure that I will have to build a function that verifies that my selction in my report is not inluded in the stop table.

                                                                                                                         

                                                                                                                        Dan

                                                                                                        • Check for date intervals

                                                                                                          Hello Dan,

                                                                                                          I forgot: Congratulation for upgrading. You will detect a lot of new feature. My two favs are the SET Analysis and triggers. And the server issues are much more professional.

                                                                                                          Enjoy, Roland

                                                                          • Check for date intervals
                                                                            Dan-Ketil Jakobsen

                                                                            Hi Roland

                                                                            Again I have to thank you for your patience with a novice like me. Aappriciate it.

                                                                            I am sorry for the confusing posts I may have written.

                                                                            To date no longer holds 99999 entries, I have set a code that changes them to 2 years after from date

                                                                            I have a created an application file, but not sure how i can send it to you.

                                                                             

                                                                            Dan