topic Re: Load only first or last 5 in QlikView

Load only first or last 5

Thu, 21 Mar 2013 11:02:37 GMT

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

Customer	Avg Interval
Customer X	6
Customer Y	9

(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

Thu, 21 Mar 2013 13:05:56 GMT

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

Thu, 21 Mar 2013 13:23:49 GMT

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

Thu, 21 Mar 2013 13:27:23 GMT

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

Thu, 21 Mar 2013 15:13:17 GMT

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 ?

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 in the FirstSortedValue with a calculation?

Re: Load only first or last 5

Thu, 21 Mar 2013 16:20:32 GMT

Try using dollar expansion. Maybe:

FirstSortedValue(DeliveryDate, -DeliveryDate,

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

Re: Load only first or last 5

Thu, 21 Mar 2013 16:46:41 GMT

That indeed helps Juan Gerardo.

But if I do this, it suddenly count all DeliveryDates, instead of those belonging to each Customer? (all return value 5)

So how should I change this piece of code then?

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

Thanks in advance