Work out if a particular day falls between two dates.

[quilldew]

Hi All,

I have a table with 2 dates in it, and this is linked to another (by a unique ID 'MyID') table containing all the dates between these 2 dates, which is then linked to a calendar table, so I can count the number of days between the dates within any given period within the calendar. This all works fine.

Now if I select a particular week (worked out already by the calendar) I want show whether the Thursday of that week is counted for each 'MyID' that has days that fall within that week.

Does that make sense?

I also want to shift the week looked at back by 3 days to the preceding Friday, and count the number of days included between that Friday and the Thursday of the selected week.

This even sounds confusing to me so i'll make up a mock .qvw and post it. Please feel free to throw out ideas in the meantime

[shekhar_analyti]

Attach sample data please

[quilldew]

Hi,

1)

So in the 'Details' Table I would like to include a Boolean that shows 'Y' if the Thursday AND Friday are included as ReferenceDate's for the week selected in the 'Weekly Count' table.

2)

There is a count in the 'Details' table that counts the number of ReferenceDate's that fall within the week selected in the 'Weekly Count' table. I would also like to include a count that shifts the week looked at backwards by 3 days, ie: back to the preceding Friday, and counts how many ReferenceDate's fall within that date and the Thursday within the week selected in the 'Weekly Count' table.

Whew.

[sunny_talwar]

Would you be able to provide the numerical output you are looking for (may be in an Excel file)?

[quilldew]

Hi Sunny,

Please see attached excel doc with expected output for selected week beginning 22/01/2018.

[sunny_talwar · Accepted solution]

Check attached

[quilldew]

You, Sunny, are a Genius.

Cannot thank you enough. Until next time