# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## 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.

Tags (1)
1 Solution

Accepted Solutions
MVP

## 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;

12 Replies
MVP

## 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;

Not applicable

## 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

(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

New Contributor

## Re: Date Difference Calculation

Hi Sir Sunny,

Thanks for you answer. It really helps a lot.

MVP

## 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

New Contributor

## Re: Date Difference Calculation

Hi sunindia,

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.

MVP

## 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;

New Contributor

## Re: Date Difference Calculation

Hi sunindia‌,

Thanks Again! That worked.

Regards,
Damien

MVP

No worries

Best,

Sunny

New Contributor

## 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.