In Qlik Sense, I have a table PlanningItems. The unique Id here is under PlanningItem-Id. Here is a time schedule for each Account-Id with a PlanningItems.StartDate and PlanningItems.EndDate. Per Order-Id, it indicates, whether it is a PlanningItems.TravelDaysStart where 1 is a yes, and whether it is a PlanningItems.TravelDaysEnd, where 1 is a yes. Now I need to create a count of the Traveldays per Order-Id, at the Account-Id level, which works, however, the problem is in the following. If a PlanningItems.EndDate connects to a PlanningItems.StartDate if the Account-Id is the same and an Order-Id is entered, this should be seen as 1 travelday, not two. How to make this!
Below the data and TravelDays now, and what i need/expect:
Can you please share the calculation used for expected Travel Days.
and one more thing Both start date and end date are in same table right Planningitem table , but you used connects in your question, it seems that two dates are in different tables?
The Data is in the same table, PlanningItems, sorry for the inconvenience.
The TravelDays Now, is just a count of the columns PlanningItems.TravelDaysStart and PlanningItems.TravelDaysEnd.
TravelDays Expected isn't a calculation, it's just the outcome I need.
The problem is, is that now, for 23/07/2023 and 24/07/2024 two days are calculated as TravelDay. However, it is a consecutive date, because one travels from one order to another, so it should be considered as 1 travel day..
For Order 12384898975281936, two travel days is correct, because there is no consecutive order date from another order. I hope this makes things clear!
