Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

FirstSortedValue calculate field [N]?

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.

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

6 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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;