12 Replies Latest reply: Aug 10, 2015 10:40 PM by Sunny Talwar

Date Difference Calculation

Hi All,

Good Day!

I am currently working on a date calculation using QlikSense, I have a Customer column and an Order Date Column, and I need to calculate for the date difference between the customer orders and at the same time the average of the date difference of their orders. Please refer to the example below regarding the output of the date difference I am calculating for.

I've been working on this for quite a long time now but I can't get the result I wanted to see.

Hope you can help me on this. Thanks in advance for your help everyone.

• Re: Date Difference Calculation

Try the following Script:

Table:

Customer, Order Date

A, 1/2/2015

A, 1/5/2015

A, 1/10/2015

B, 5/6/2015

C, 3/2/2015

C, 3/20/2015

C, 4/1/2015

C, 4/8/2015

D, 1/8/2015

D, 1/13/2015

];

Join(Table)

[Order Date],

If(Customer = Peek(Customer), [Order Date] - Peek('Order Date')) as [Date Difference]

Resident Table

Order By Customer, [Order Date];

FinalTable:

NoConcatenate

[Order Date],

If(Len(Trim([Date Difference])) = 0, 0, [Date Difference]) as [Date Difference]

Resident Table;

Join(FinalTable)

Avg([Date Difference]) as [Avg Difference]

Resident FinalTable

Group By Customer;

DROP Table Table;

• Re: Date Difference Calculation

Hi Sir Sunny,

Thanks for you answer. It really helps a lot.

• Re: Date Difference Calculation

No problem Damien

I am glad I was able to help.

Best,

Sunny

P.S. you can call me just Sunny

• Re: Date Difference Calculation

Hi stalwar1,

Just an adjustment, What will I change if I don't want to include the first OrderDate into the calculation of Average Date Difference?  Please refer below for the new calculated output.

• Re: Date Difference Calculation

Try this:

Table:

Customer, Order Date

A, 1/2/2015

A, 1/5/2015

A, 1/10/2015

B, 5/6/2015

C, 3/2/2015

C, 3/20/2015

C, 4/1/2015

C, 4/8/2015

D, 1/8/2015

D, 1/13/2015

];

Join(Table)

[Order Date],

If(Customer = Peek(Customer), [Order Date] - Peek('Order Date')) as [Date Difference]

Resident Table

Order By Customer, [Order Date];

Join(Table)

Avg([Date Difference]) as [Avg Difference]

Resident Table

Group By Customer;

• Re: Date Difference Calculation

Hi stalwar1,

Thanks Again! That worked.

Regards,
Damien

• Re: Date Difference Calculation

No worries

Best,

Sunny

• Re: Date Difference Calculation

Hi sunindia,

I have another question, how will I determine that last orderDate of my customers and then compute for the Days since their last order. I've been trying the bottom and Max function but I can't get it right. I just want to calculate for the Days Difference from the Last OrderDate and subract it to the Date today to get the Days since last Order,  please refer to the result below that I want to see:

I hope you can still hep me. Thanks.

• Re: Date Difference Calculation

Try this:

Table:

Customer, Order Date

A, 1/2/2015

A, 1/5/2015

A, 1/10/2015

B, 5/6/2015

C, 3/2/2015

C, 3/20/2015

C, 4/1/2015

C, 4/8/2015

D, 1/8/2015

D, 1/13/2015

];

Join(Table)

[Order Date],

If(Customer = Peek(Customer), [Order Date] - Peek('Order Date')) as [Date Difference]

Resident Table

Order By Customer, [Order Date];

Join(Table)

Avg([Date Difference]) as [Avg Difference]

Resident Table

Group By Customer;

Join(Table)

Num(Today()) - MaxOrderDate + 1 as [Days since Last Order];

Max([Order Date]) as MaxOrderDate

Resident Table

Group By Customer;

• Re: Date Difference Calculation

Hi sunindia,

It works! Thank you so much for your help.

I appreciate your prompt response. Thanks again.

regards,

Damien

• Re: Date Difference Calculation

No problem Damien

I am glad I was able to help.

Best,

Sunny

• Re: Date Difference Calculation

hi Damien,

I tried ur Given query .. i m able to find Ur Date Difference Field.. Kinldy Check if its Help U.

Date([Order Date],'MM/DD/YYYY') as [Order Date],

FROM

[D:\Brijesh\Brijesh Case Study\FUNCTION\Date Diff.xlsx]

(ooxml, embedded labels, table is Sheet1);

After That i taken Straight Table With 2 Dimension Customer and [Order Date],

and as well 2 expression

[Order Date]

and  Date Difference ------  RangeSum(Date(Date#([Order Date],'MM/DD/YYYY'),'DD/MM/YYYY') - Above(Date(Date#([Order Date],'MM/DD/YYYY'),'DD/MM/YYYY')))

and if u dont want 2 Order Date in straight table u can Hide one of them