Need to calculate days between start and end date using above max end date

[shabarish0587]

Hi Folks,

                 I need to display days between start and end date for my data. in my data i have columns like F_ID,U_ID,Start_Date,End_Date , in below example while calculating days we need to take max of previous end date and calculate days . generally first U_ID U878 has started on 04-01-2021 and ended on 02-02-2021 but second row UID U137 has started on 01-02-2021 so here we need to take days between current start and above end date value so if we observe start date is less than above end date we need to display zero other wise if we have start date is greater than above max of end date we need to display number of that between that. i need to display this date

For eg:

     U_id U007 start_date is 01-03-2021 and for end date we need to take above maximum of end date that is 26-02-2022 and start date-end date will get 3 value. we have another example in same table for U387 start date is 02-04-2021 and if we observe above max end date is 30-03-2022 we will get output is 3 like that we need to calculate. can anyone help me on this how to take above max end date.

F_ID	U_ID	Start_Date	End_Date	Expected Output
F100	U878	04-01-2021	02-02-2021	0
F100	U137	01-02-2021	24-02-2021	0
F100	U133	01-02-2021	26-02-2021	0
F100	U560	09-02-2021	12-02-2021	0
F100	U268	23-02-2021	26-02-2021	0
F100	U007	01-03-2021	30-03-2021	3
F100	U009	01-03-2021	30-03-2021	3
F100	U526	04-03-2021	12-03-2021	0
F100	U387	02-04-2021	02-04-2021	3
F100	U868	05-04-2021	27-04-2021	2

[edwin · Accepted solution]

the business rule is too confusing as stated.  however, as a general rule, if you want to get some aggregation of rows relative to A single row, the design pattern is the same:

1. do a full outer join to relate one row to all other rows
2. filter out irrelevant rows so that each row is related only to all relevant rows
3. aggregate all relevant rows
4. left join to the original set of rows to get your answer.

look at this sample:
Solved: Re: Count of occurrences of an event within 1 year... - Qlik Community - 1988843

as i cant fully understand the business rules for other rows, let me describe what will happen with just one of the business rules above for U007

1. if you do a full outer join, 007 will become related to 9 rows
2. if i understand it correctly, you filter out rows you dont need: by above im guess start date < 01-03-2021, you are left with 5 rows
3. this step is a bit complicated as you will need to get the max date and then you need to get the start date of the max date - maybe you can do an applymap
4. then you left join the result to step 3.

what i dont get is why U262 is 0 when you can get the max of the rows before it and you can get the diff.  all the business rules arent defined and everyone is left guess.  if you supply everything thats needed maybe someone can step in with code.