calculate time difference between different rows with linked documents

[djih]

Hello folks,

I have the following challenge. I have to present the time difference between linked receipts. this is how my data looks like:

Date	DocNo	DocType	Amount	Turnover	Quote	Order	Bill
22.01.2019	100001	Quote	200	2300	100001	200001	400001
22.01.2019	200001	Order	200	2300	100001	200001	400001
24.01.2019	400001	Bill	200	2300	100001	200001	400001

In this example all this rows belong to one sales process. 100001 is the initial quote, followed by the order from customer (200001) on the same day. After 2 days the goods are delivered and the bill 400001 is generated. This is why we have the last 3 columns to link the documents.

My target is to calculate the days between each step (from quote to order; from order to bill):

Date	DocNo	DocType	Amount	Turnover	Quote	Order	Bill	QuoteToOrder	OrderToBill
22.01.2019	100001	Quote	200	2300	100001	200001	400001	0	2
22.01.2019	200001	Order	200	2300	100001	200001	400001	0	2
24.01.2019	400001	Bill	200	2300	100001	200001	400001	0	2

I have tried the following:

QuoteToOrder: Max({<DocType={Order}>}Date) - Min({<DocType={Quote}>} Date)

OrderToBill: Max({<DocType={Bill}>}Date) - Min({<DocType={Order}>} Date)

In the individual rows the result is '-' but if I activate column-results the column result have the correct numbers (0,2). So my basic idea works, but i need the information redundant in each column.

Can anyone help, please?

[Brett_Bleess]

Christoph, any chance you can attach the QVW on this one?  I suspect that would help you get some responses, as it helps if folks can see the data model you have etc. in order to try to come up with the right way to go at things for the use case.  My post will kick things back up, but my hunch is the QVW would really help in this case.

The best I have to try to help would be the following:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Regards,
Brett