Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

DateComputation.PNG

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.

1 Solution

Accepted Solutions

Re: Date Difference Calculation

Try the following Script:

Table:

LOAD * Inline [

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)

LOAD Customer,

  [Order Date],

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

Resident Table

Order By Customer, [Order Date];

FinalTable:

NoConcatenate

LOAD Customer,

  [Order Date],

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

Resident Table;

Join(FinalTable)

LOAD Customer,

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

Resident FinalTable

Group By Customer;

DROP Table Table;

12 Replies

Re: Date Difference Calculation

Try the following Script:

Table:

LOAD * Inline [

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)

LOAD Customer,

  [Order Date],

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

Resident Table

Order By Customer, [Order Date];

FinalTable:

NoConcatenate

LOAD Customer,

  [Order Date],

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

Resident Table;

Join(FinalTable)

LOAD Customer,

  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.

LOAD Customer,

     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



Date.png

teambateam
New Contributor

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

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

DateComputation1.PNG

Thanks again in advance.

Re: Date Difference Calculation

Try this:

Table:

LOAD * Inline [

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)

LOAD Customer,

  [Order Date],

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

Resident Table

Order By Customer, [Order Date];

Join(Table)

LOAD Customer,

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

Resident Table

Group By Customer;

teambateam
New Contributor

Re: Date Difference Calculation

Hi sunindia‌,

Thanks Again! That worked.

Regards,
Damien

Re: Date Difference Calculation

No worries

Best,

Sunny

teambateam
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:DateComputation2.PNG


I hope you can still hep me. Thanks.

Community Browser