Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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