STOPPING DATA SET AFTER A CERTAIN DATE

[tomtingley]

Hi,

I'm not sure if this is possible, but it would be amazing if you could create a workflow with the attached data set, ending the data for each row when the 'End Date' is and only starting from when the 'Start Date' is.

For example, in the first line, the 'End Date' is 31st December 2024, so I don't want to see any numbers on the row after that date.

Another example, in the second line, the 'Start Date' is 30th September 2023 and the 'End Date' is 1st October 2025, so I only want to show values for between those dates.

If anyone could solve this, that would be amazing! I've highlighted the relevant 'Start Date' and 'End Date' columns.

Thanks,

Tom

[binuacs]

@tomtingley Can you check if the result below is correct or not? If not make the changes in the formula tool accordingly

[tomtingley]

@binuacs Thanks! This does work.

Would you also be able to show for my second example in the same workflow so both lines come through in the same sheet, one after the other.

"Another example, in the second line, the 'Start Date' is 30th September 2023 and the 'End Date' is 1st October 2025, so I only want to show values for between those dates."

Sorry if this is impossible!

[Qiu · Accepted solution]

@tomtingley 
I think we can do it by combination of CrossTab and Transpose.
i also considered the case that Exit Date is null

Hope it works for you.

[binuacs · Accepted solution]

@tomtingley Updated the workflow

[tomtingley]

@Qiu @binuacs These both work amazing! Thankyou!

[tomtingley]

@Qiu @binuacs Sorry to ask again because you have both been amazing, but is there a way to take the workflow attached, incorporate the 'Start Date' and 'End Date' as mentioned above, but only return rows that show zero's within the start and end date ranges?

eg, Row 1 start date is 1st July 2023 and end date is 31st July 2024, I would want this row to show in the output if there was a zero showing within this date range. 

Whereas if there was no zero's within that date range, I wouldn't want to show it.

Hope this makes sense.

[Qiu]

@tomtingley 
I think it is possible. but I do need you to answer below

1. In the data range, it should only Year and month, it is safe we say it will the first day of that month in order to check it is in the range?

Ex: Mar 25 --> 2025-03-01

2. How should we convert this to a date?
FY 2026

[binuacs]

@tomtingley I updated in the same workflow

[tomtingley]

@binuacs Thankyou! Let me try.

To answer your question, @Qiu I would ignore FY 2026, 2027 and 2028 for now.