Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Is it possible to calculate the
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
If I put this piece of code between $( ....... ) it counts all DeliveryDates, not keeping in mind my Dimension 'CustomerID'
How can I calculate this
Thanks in advance.
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;
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.
If it is Straight table try Presentation - Max Number(Qv v.10)
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
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
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
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
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.
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;