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:


      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



        • 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?