6 Replies Latest reply: Mar 21, 2013 12:46 PM by Willem Classens RSS

    Load only first or last 5

      Hi all,

       

      In my load script, I have the following code:

      IntervalProductX:

      Load Customer, DeliveryDate

      ,if(peek(Customer)=Customer,-peek(DeliveryDate)+DeliveryDate) as Interval

      resident CustomersDB

      where Artikelidentification = 'PRODUCT X'

      order by Customer, DeliveryDate;

       

      This creates an Interval of the DeliveryDate per customer who gets Product X delivered.

      (If necessary I can add asc / desc behind the order by, so it could be first or last 5)

       

      Now I would like to show in a straight table, the average Interval per Customer over the last 5 DeliveryDate (1-5).

      And in a second column with the avarage over DeliveryDate 6-15.

       

      How can I calculate this in an expression or in the load script?

       

      If I use the above code with "First 5" before it and order it by desc, it doesn't load the first 5 from each customer.

       

      the straight table should look like

      CustomerAvg Interval
      Customer X6
      Customer Y9

       

      (don't need the dates, just the avarage of those last 5 Intervals)

       

      I hope my question is clear.

      Could someone help me with this?

       

      Thanks in advance

       

      Willem

        • Re: Load only first or last 5
          Juan Gerardo Cabeza Luque

          I suggest you use the Class() function in a calculated dimension. It should be something like:

           

               Class(Interval, 5, 'DeliveryDate')

           

          Even you can make the interval range dynamic, if you use a variable instead of a fixed 5.

           

           

          Juan Gerardo

            • Re: Load only first or last 5

              Thanks for your reply Juan Gerardo Cabeza.

              I don't see how I could implement this in my scenario.

               

              I'm creating a straight table with the following fields:

               

              "Customer", "MaxDeliveryDate", "Avg Interval over last 5 delivery dates" and a few more fields.

               

              there are about 120 customers in this table.

              I would like to see if their average interval over the last 5 delivery dates, change in comparison of the avg interval over the 10 delivery dates before them.

               

              If I use the expression avg(Interval), the average interval over all the delivery dates is taken, but some order for more then 3 years.

               

              I don't yet see how this class code could help me with this.

               

               

              Since I can calculate per customer what their 5th last delivery date was, I was thinking about an expression like this:

               

              =if((DeliveryDate)>FirstSortedValue(DeliveryDate, -DeliveryDate,5),avg(Interval))

               

              But this comes up with nothing.

              Plus that if it's a new customer who only has 3 DeliveryDates yet, the firstsortedvalue returns "-" (it doesn't exists yet)

               

              Hopefully I made myself more clear, or maybe your way is the right way, but you could explain it a little more to me.

               

              Thanks in advance.

                • Re: Load only first or last 5
                  Juan Gerardo Cabeza Luque

                  You have to use the Class() function as a Calculated Dimension, not expression. Maybe if you can attach some example qlikview I can have a look, and show you how to do it.

                    • Re: Load only first or last 5

                      That is was a calculated dimension, I am aware of.

                       

                      I almost got it working I think.

                       

                      The only thing is, is it possible to add a calculated value behind the FirstSortedValue (the [N]?

                       

                      I got a piece of code which calculates if the number "5" is there. If so, the number 5 is filled in, if there are only for example 3 DeliveryDates, it fills in 3. But I can't get that piece of code implemented in this FirstSortedValue

                      So the following piece should be editted:

                      FirstSortedValue(DeliveryDate, -DeliveryDate,5)

                      to for example

                       

                      FirstSortedValue(DeliveryDate, -DeliveryDate,

                      if(count({<[Artikelidentification]={'Product X'}>} DeliveryDate)>5,5,count({<[Artikelidentification]={'Product X'}>} DeliveryDate))

                       

                      If I get this to work, I can calculate the average interval over the last 5 dates.

                       

                      So how can I replace the [N] in the FirstSortedValue with a calculation?