topic Re: Derive previous billing dates in QlikView

Derive previous billing dates

Fri, 22 Apr 2016 15:05:25 GMT

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.

Re: Derive previous billing dates

sunny_talwar — Fri, 22 Apr 2016 15:09:20 GMT

You can use Peek or Previous functions

Peek() or Previous() ?

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

Re: Derive previous billing dates

Kushal_Chawda — Fri, 22 Apr 2016 15:10:30 GMT

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;

Re: Derive previous billing dates

Mon, 25 Apr 2016 13:49:25 GMT

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 !

Re: Derive previous billing dates

Kushal_Chawda — Tue, 26 Apr 2016 11:57:58 GMT

Can you able to share the sample data?

Re: Derive previous billing dates

swuehl — Tue, 26 Apr 2016 12:28:31 GMT

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

Re: Derive previous billing dates

Tue, 26 Apr 2016 14:11:24 GMT

Thanks so much! works perfectly!

Re: Derive previous billing dates

swuehl — Tue, 26 Apr 2016 16:28:22 GMT

You are welcome.

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

Qlik Community Tip: Marking Replies as Correct or Helpful