7 Replies Latest reply: Jan 19, 2012 10:52 AM by Stefan Wühl RSS

    avg duration between dates

      Hi all,

       

      I'm just starting to learn Qlikview.

      I have a problem to which I can't find the answer.

       

      I have different dates belonging to a customer.

       

      For example, "customer A" has ordered his products on 01-01-2011, 01-02-2011, 5-03-2011 etc.

      Is it possible to calculate the avarage days between these dates?

      It are a lot of dates, but they are in the same field.

       

      =Date(Max(Orderdate)) gives the last orderdate of person A (in my pivot table I have at first the selection of the customer)

       

      I want to make a prediction of when the customer is placing their new order (so max Orderdate plus the 'avg date between orders').

       

      Hopefully someone is able to help me / set me on the right track.

       

      Thanks in advance,

      Willem

       

      Ps. I can't place the file online, since the information is confidential.

        • Re: avg duration between dates
          Stefan Wühl

          Hi Willem,

           

          please have a look at attached app. I calculated the intervals at script level using sorted tables and peek function.

           

          like this:

           

          INPUT:

          LOAD * INLINE [

          Customer, OrderDate

          A, 01-01-2011

          B, 01-01-2011

          A, 01-02-2011

          A, 05-03-2011

          B, 02-03-2011

          A, 02-04-2011

          B, 15-05-2011

          ];

           

          Result:

          Load Customer, OrderDate

          ,if(peek(Customer)=Customer,peek('OrderDate')-OrderDate) as Interval

          resident INPUT order by Customer, OrderDate desc;

           

          drop table INPUT;

           

          Regards,

          Stefan

            • avg duration between dates

              TillooThanks Stefan,

               

              That looks exactly what I want. Now I have to implement it in my own file.

              You put the customer and orderDate in the script-file.

               

              My original file loads allready the dates and Costumers from an excel file.

               

              Directory;

              LOAD    [Name adress],

                   DATE#(Orderdate,'D.M.YYYY') as Orderdate,

                    ...

                    ...

                    ...

              FROM

              OriginalFile\*.*

              (biff, embedded labels, table is Blad1$, filters(

              Remove(Row, RowCnd(CellValue, 10, StrCnd(null))),

              Remove(Row, RowCnd(CellValue, 12, StrCnd(null)))

              ));

                 

              (offcourse with a bunch of otter stuff on the dots)

               

              Till now on, it's not succeeded to implement it, but I will give it another try tomorrow.

               

              tried different things:

               

              Result:

              Load [Name-adress], Orderdate

              ,if(peek([Name-adress])=[Name-adress],peek('Orderdate')-Orderdate) as Interval;

               

              beneath my own code (which someone else made for me), but that didn't work.

               

              It worked to only insert if(peek(......) as interval between the other lines (on the dots), but I got funny outcomes (-0 till -3000 or so)

                • Re: avg duration between dates
                  Stefan Wühl

                  It seems that you need to parse in the OrderDate as Date before doing the calculations / subtraction.

                   

                  I think you need something like

                  DATE#(peek('Orderdate'),'D.M.YYYY')-DATE#(Orderdate,'D.M.YYYY')

                   

                  edit:

                  (You need this because your original parsing of OrderDate on top of your Load statements will not affect the following lines, they will still use original OrderDate as read in from Input, not the parsed one).

                    • avg duration between dates

                      Stefan,

                       

                      Today I tried your solution again, and finally I got it to work

                      But now I have one 'small' problem...

                      Sometimes a costumer gets our products, but then stops.. after, for example, a half year, the customer starts buying our products again.

                       

                      In my interval, he buys products each 10 - 15 days, but because of the half year stop, one interval is for example 184 days. This gives an incorrect avarage interval (should be 12,5, but ends up way higher), especially costumers that haven't bought so much (not so many values).

                       

                      Is there a possibility to get rid of these strange values (if not in line with the rest, reject the value or change the value to an avarage value)

                        • Re: avg duration between dates
                          Stefan Wühl

                          Should be no problem.

                           

                          SInce we calculated the intervals already in the script, you could exclude e.g. intervals larger than 100 days in your calculation like:


                          =avg(if(Interval < 100,Interval))

                           

                          or the same using set analysis:

                           

                          =avg({<Interval = {"<100"}>} Interval)

                           

                          In attached sample, I created one outlier for Customer A to demonstrate the effect of the filter.

                          Using these expression, you just remove the value from the set to average, so your set is one item smaller in my sample.

                           

                          Hope this helps,

                          Stefan

                            • Re: avg duration between dates

                              could be a solution indeed, but it's not possible to make it a kind of formula so you don't have any hard data?

                              Because some people buy each 90 / 100 days, while others normally buy each 10 days, so fo those with 90 /100 days, 100 is normal, while for those with 10 days normal, 80 days is a much to high value.

                               

                              In most cases the 100 value works, but if I can calculate an avarage x 1,5 or so, then it's more correct.

                               

                              If not possible, I will work with the 100, it does the trick for me (but learning extra is never wrong )