Date Difference Calculation

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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;

[#3]

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

[Anonymous]

Hi Sir Sunny,

Thanks for you answer. It really helps a lot.

[sunny_talwar]

No problem Damien‌

I am glad I was able to help.

Best,

Sunny

P.S. you can call me just Sunny

[Anonymous]

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.

Thanks again in advance.

[sunny_talwar]

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;

[Anonymous]

Hi sunindia‌,

Thanks Again! That worked.

Regards,
Damien

[sunny_talwar]

No worries

Best,

Sunny

[Anonymous]

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.