3 Replies Latest reply: Jan 28, 2014 2:10 PM by Gysbert Wassenaar RSS

    Buying cycles.

    David Young

      Does anyone have some good examples of how to handle buying cycles.  I have a client that buys their first product in Jan. How long is it before they buy again and what product did they purchase. Amy ideas would be welcome.

       

      Thanks

      David

        • Re: Buying cycles.
          Gysbert Wassenaar

          Load the data ordered by client and then by purchase date. You can use the previous() function to get the previous purchase date so you can calculate the difference between the two dates. Something like this:

           

          Temp:

          Load client, date, product

          From ...mysource...;

           

          Result:

          Load client, date, product,

              if(previous(client)=client, interval(rangesum(date, -previous(date)),'d')) as cycle_days,

              if(previous(client)=client, previous(product)) as previous_product

          Resident Temp

          order by client, date;

           

          drop table Temp;

            • Re: Buying cycles.
              David Young

              I am still new to QlikView so I am not sure how to use the formula.  I understand the syntax.  I have tried your option but I am getting syn-key's.  I will need to play with it some more.

               

              David

                • Re: Buying cycles.
                  Gysbert Wassenaar

                  The previous function retrieves a value from the previous record of the source table. It's used to check we're still dealing with the same client so we don't subtract dates from different clients. It's also used to get the date from the previous record so it can be subtracted from the date value of the current record.

                   

                  The interval function is merely to format a date (or time) interval, in this case in days.

                   

                  The rangesum function takes simply sums all the arguments passed to it. Null values are ignored, which is how it differs from using the + operator.

                   

                  Did you include the drop table Temp line in your script? Not doing so would cause a synthetic key. It's also possible you loaded another table that has several fields in common with the Result table. In that case you'll have to decide if the tables are associated correctly. Synthetic keys are not per definition wrong, but they do often indicate a problem in the data model.