Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
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
Try using dollar expansion. Maybe:
FirstSortedValue(DeliveryDate, -DeliveryDate,
$(=if(count({<[Artikelidentification]={'Product X'}>} DeliveryDate)>5,5,count({<[Artikelidentification]={'Product X'}>} DeliveryDate)))
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