Derive previous billing dates

[#1]

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.

[sunny_talwar]

You can use Peek or Previous functions

Peek() or Previous() ?

Peek() vs Previous() – When to Use Each

[Kushal_Chawda]

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;

[#4]

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 !

[Kushal_Chawda]

Can you able to share the sample data?

[swuehl]

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

[#7]

Thanks so much!  works perfectly!

[swuehl · Accepted solution]

You are welcome.

If your request is resolved, please consider closing this thread:

Qlik Community Tip: Marking Replies as Correct or Helpful