Rangeavg

[P_Kumar]

Hi,

I have following  requirement:

- For a Vendor +  Hub  +  Product combination

- Need to derive expected date of delivered date  for those products which are not yet delivered.  This should be done based on the avg days of historical delivered dates of the above said combination Vendor +  Hub  +  Product

-  I have tried doing rangeavg without success.  Basically, rangeavg will give us the avg days a shipment took for above combination and it should be updated against the row where delivered date is null (not delivered yet).  Subsequently, we can add these avg days+ Entry date which will give us the expected date of delivery for that undelivered row.

  I have attached sample data set and qvf file.  

Can someone kindly help with this.  Thanks.

@sunny_talwar 

[tresesco]

You can use below exp for average calculation:

if(
sum(interval(date(Delivered_Date)-date(Entry_Date),'d'))=0,
avg(total interval(date(Delivered_Date)-date(Entry_Date),'d')),
'NA')

[P_Kumar]

Thank you @tresesco 

using this formula, I got the Sum(date deviation)/12.  Having mentioned in my request, how can I get the results segregated for the selected combination of  Vendor +  Hub  +  Product combination.

I have attached revised excel explained how the results should look like.  Thanks again for your prompt response.

[tresesco · Accepted solution]

This?

if
(sum(interval(date(Delivered_Date)-date(Entry_Date),'d'))=0,
avg(total <Vendor, Hub, Product> interval(date(Delivered_Date)-date(Entry_Date),'d')),'NA')

[P_Kumar]

Edited this post:

Yes, perfect, thank you @tresesco 

I am trying to derive STDEV (Standard Deviation) of the field 'Delivered Dt- Entry Dt'.  I am getting the value as 18.96 in totals but I want this to be get updated against 'No delivery date' rows by the same combination Vendor+ Hub+ Product. Attached the pic. Can you please help with this. Thanks.

[P_Kumar]

I got the solution.. thank you.