topic Re: FirstSortedValue calculate field [N]? in QlikView

FirstSortedValue calculate field [N]?

Fri, 22 Mar 2013 09:16:40 GMT

Hi all,

Is it possible to calculate the value for FirstSortedValue?

If I fill in 10 for the [N} value, but there aren't 10 records on that CustomerID, then it should give back the max available number.

This piece of code works as an individual expression to count the max DeliveryDate a Customer has:

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

When I add it as value in my FirstSortedValue, it doesn't work.

If I put this piece of code between $( ....... ) it counts all DeliveryDates, not keeping in mind my Dimension 'CustomerID'

How can I calculate this field, per CustomerID?

Thanks in advance.

Re: FirstSortedValue calculate field [N]?

Mon, 25 Mar 2013 08:12:14 GMT

Does nobody has an possible answer to this?

If I put an aggr before it, it also doesn't work

So for example

$(=aggr(if(count({<[Artikelidentification]={'Product X'}>} DeliveryDate)>=10,10,count({<[Artikelidentification]={'Product X'}>} DeliveryDate)),Customer)), this gives me "-".

Please could someone make me clear what I'm doing wrong?

If I make it $('.....'), this also doesn't work.

Re: FirstSortedValue calculate field [N]?

Mon, 25 Mar 2013 08:29:57 GMT

If it is Straight table try Presentation - Max Number(Qv v.10)

Re: FirstSortedValue calculate field [N]?

Mon, 25 Mar 2013 08:53:18 GMT

Thanks for your answer, but this isn't a possibility for me.

I'm creating a firstsortedvalue calculation:

=firstsortedvalue( ... , .... ,$(aggr(count({<[Geleverd artikel]={'402400'}>} Leverdatum),Leverklant)) )

So I would like to calculate the value, because if I fill in '10' I get an '-' for some customers, because they don't have 10 orders.

So for someone who has only 8 orders, I want to fill in 8 for .

What I want to do with this:

I would like to calculate the average interval for the last 10 dates.

If I take the =(max date - firstsortedvalue(..,.., 10) ) / number of deliveries (10 or 😎

then I have the average interval over the last 10 dates, but for someone with 8 deliveries, the 10 should turn into 8.

Perhaps I'm trying to do this on a completely wrong way and should I take a different approach:

In my script, I have a calculation for the Interval also, but this calculates the interval for each delivery, as far back as possible. So an average of this field shows a possible interval over the last 3 years. I don't succeed to use this part of code to get only the last 10 intervals. (This gives me a good view on how the order-pattern of a customer changes and if actions should be taken)

my script code to calculate this interval:

IntervalProductX:

Load Customer, Customer

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

resident Deliveries

where Artikelidentification = 'Product X'

order by Customer, DeliveryDate;

So perhaps I should expand this load script to make an succesfull calculation for the last 10 DeliveryDate

I really need to get this to work.

Thanks in advance.

edit:

Perhaps adding something like this?:

IntervalProductX10:

LOAD Customer, avg(Interval) as Interval10

Resident IntervalProductX

where Customer= Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Customer)))))))))

order by Customer, DeliveryDate;

Message was edited by: nijsen-granico Added new possible piece of load-script

Re: FirstSortedValue calculate field [N]?

Tue, 26 Mar 2013 11:07:14 GMT

Hello,

Can you please attach you qvw. or try some below approaches :

1) I am thinking to somehow set an Variable with the result of the aggregation and then pass it to the FirstSortedValue function.

2) Or create a new column in the Load Script which can have rank of rows, so that we can use the max value of rank to FirstSortedValue function.

Thanks,

itsangad

Re: FirstSortedValue calculate field [N]?

Wed, 27 Mar 2013 07:10:59 GMT

Thanks Itsangad,

I can't attach the qvw.

I don't yet know how I can atttach a calculated value to the FirstSortedValue.

It accepts only hard numbers for like 1, 2, 3 etc..

I'm also thinking about ranking.

In the load script I'm trying to do the following (I was also thinking about ranking), this example creates the average over the last 3 to keep it more simple.

IntervalProductX:
Load Customer, DeliveryDate
,if(peek(Customer)=Customer,-peek(DeliveryDate)+DeliveryDate) as Interval
resident DeliveriesDB 
where Artikelidentification = 'ProductX'
order by Customer, DeliveryDate;
IntervalProductXCounter:
LOAD Customer, Interval, DeliveryDate,
     if(Previous(Customer)=Customer,numsum(peek('Counter'),1),1) As Counter
Resident IntervalProductX
order by Customer;
MaxCounter:
LOAD
Max(Counter) as MaxCounter
resident IntervalProductXCounter
group by Customer;
DataFinal:
load *,
     if(MaxCounter>=3,RangeAvg(Interval ,Peek('Interval'),Peek('Interval',-2)),if(Counter=2,RangeAvg(Interval,Peek('Interval')),Interval) ) as Last3AvgInterval
resident IntervalProductXCounter
order by Customer;

I need to calculate the avg only for the max value (maxCounter).

So if Customer Y has 130 deliveries, the IntervalProductX counter will count and comes to a maximum of 130.

This 130 needs to be taken and an average of the interval of Counts 128, 129 and 130 need to be calculated.

So perhaps this might be the way to approach this problem?

Thanks in advance.

Message was edited by: nijsen-granico Edited the piece of code a bit.

Re: FirstSortedValue calculate field [N]?

Wed, 27 Mar 2013 14:52:16 GMT

Finally got it working.

I used the following code: (DeliveryDB is my main database that contains the thousands of deliveries)

join (DeliveryDB)
Load Customer, DeliveryDate
,if(peek(Customer)=Customer,-peek(DeliveryDate)+DeliveryDate) as Interval
resident DeliveryDB
where Artikelidentification = 'ProductX'
order by Customer, DeliveryDate;
join(DeliveryDB)
Load Customer, DeliveryDate,
     if(peek(Customer)=Customer, numsum(peek('Counter'),1),1) As Counter 
Resident DeliveryDB
where Artikelidentification = 'ProductX'
order by Customer, DeliveryDate;
join(DeliveryDB)
load Customer, Counter, Interval,
    if(Counter>5,RangeAvg(Interval ,Peek('Interval'),Peek('Interval',-2),Peek('Interval',-3),Peek('Interval',-4)),
    if(Counter=5,RangeAvg(Interval ,Peek('Interval'),Peek('Interval',-2),Peek('Interval',-3)),
    if(Counter=4,RangeAvg(Interval ,Peek('Interval'),Peek('Interval',-2)),
    if(Counter=3,RangeAvg(Interval,Peek('Interval')),Interval) )))  as Rolling5Avg,
   
   if(Counter>15,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11),Peek('Interval',-12),Peek('Interval',-13),Peek('Interval',14)),
   if(Counter=15,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11),Peek('Interval',-12),Peek('Interval',-13)),
   if(Counter=14,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11),Peek('Interval',-12)),
   if(Counter=13,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11)),
   if(Counter=12,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10)),
   if(Counter=11,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9)),
   if(Counter=10,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8)),
   if(Counter=9,RangeAvg(Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7)),
   if(Counter=8,RangeAvg(Peek('Interval',-5),Peek('Interval',-6)),
   if(Counter=7,RangeAvg(Peek('Interval',-5)),
     'N/B') )))))))))  as Rolling10Avg  //Calculating the average for the 10 deliveries before the Rolling5Avg
         
resident DeliveryDB
where Artikelidentificatie = 'ProductX'
order by Customer, Counter;