Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Pulled dates from Fact table grouped by customer, material & billing date.
Need to find out the days between orders ,for which previous billing dates ( like below) are required.
How do we derive the previous billing dates at script level?
Appreciate your help.
You are welcome.
If your request is resolved, please consider closing this thread:
Data:
LOAD Customer,
Material,
Billing_Date
FROM table;
new:
noconcatenate
LOAD *,
if(Billing_Date<>previous(Billing_Date),previous(Billing_Date),0) as PrevBillingDate
resident Data
order by Billing_Date asc;
drop table Data;
Thanks for your help. I am trying to derive standard deviation.:
1. average days of sales would be the diff between the last and first day of sale divided by number of sales.
2. The days between sales is needed to calculate the standard deviation or to use the Stdev function.
I am still not able to get days between sales as the previous(Billing_date ) is not working.
Could you please help.
Thanks !
Can you able to share the sample data?
Rajashree Reddy wrote:
Thanks for your help. I am trying to derive standard deviation.:
1. average days of sales would be the diff between the last and first day of sale divided by number of sales.
2. The days between sales is needed to calculate the standard deviation or to use the Stdev function.
I am still not able to get days between sales as the previous(Billing_date ) is not working.
Could you please help.
Thanks !
w.r.t 1), I think you should divide by (number of sales -1) to get the avg number of days between sales
w.r.t. 2): Seems to work for me:
SET DateFormat='D-M-YYYY';
LOAD *,
alt(Peek('Billing_Date'),0) as PrevBillingDate1,
alt(Previous(Date#(Billing_Date)),0) as PrevBillingDate2,
Billing_Date - Previous(Billing_Date) as BillingDateDiff
INLINE [
Billing_Date
1-4-2016
10-4-2016
22-4-2016
26-4-2016
];
Then you can calculate the avg days of sales like
=Avg(BillingDateDiff)
[returns 8.33]
and the standard deviation like
=Stdev(BillingDateDiff)
[returns 4.04]
Billing_Date | PrevBillingDate1 | PrevBillingDate2 | BillingDateDiff |
---|---|---|---|
1-4-2016 | 0 | 0 | |
10-4-2016 | 1-4-2016 | 1-4-2016 | 9 |
22-4-2016 | 10-4-2016 | 10-4-2016 | 12 |
26-4-2016 | 22-4-2016 | 22-4-2016 | 4 |
Thanks so much! works perfectly!
You are welcome.
If your request is resolved, please consider closing this thread: