I'm just answering my own question here...
We run some of our reports for weeks that run Thursday-Wednesday. Using standard JQL functions the basic functions sort of work, but when the week moves on on a Sunday, the reports are now a week ahead until the following Thursday.
None of the built-in JQL date functions allow any funny business. Trust me, I've tried it. So this is the solution I came up with. Not exactly easy to maintain...
If you have a week that runs (inclusively) Monday-Sunday or Tuesday-Monday etc you will have to modify the following...
Current Week | Coming Week | Last Week | Prior Week | ||||||||||
0 | 0 | project = CCB and ( | 7 | 7 | project = CCB and ( | -7 | -7 | project = CCB and ( | -14 | -14 | project = CCB and ( | ||
SUN | MON | 1 | 1 | (due = startofday(1d) and due = startOfWeek(1d)) or | 8 | 8 | (due = startofday(8d) and due = startOfWeek(8d)) or | -6 | -6 | (due = startofday(-6d) and due = startOfWeek(-6d)) or | -13 | -13 | (due = startofday(-13d) and due = startOfWeek(-13d)) or |
TUE | 2 | 2 | (due = startofday(2d) and due = startOfWeek(2d)) or | 9 | 9 | (due = startofday(9d) and due = startOfWeek(9d)) or | -5 | -5 | (due = startofday(-5d) and due = startOfWeek(-5d)) or | -12 | -12 | (due = startofday(-12d) and due = startOfWeek(-12d)) or | |
WED | 3 | 3 | (due = startofday(3d) and due = startOfWeek(3d)) or | 10 | 10 | (due = startofday(10d) and due = startOfWeek(10d)) or | -4 | -4 | (due = startofday(-4d) and due = startOfWeek(-4d)) or | -11 | -11 | (due = startofday(-11d) and due = startOfWeek(-11d)) or | |
THU | -3 | -3 | (due = startofday(-3d) and due = startOfWeek(-3d)) or | 4 | 4 | (due = startofday(4d) and due = startOfWeek(4d)) or | -10 | -10 | (due = startofday(-10d) and due = startOfWeek(-10d)) or | -17 | -17 | (due = startofday(-17d) and due = startOfWeek(-17d)) or | |
FRI | -2 | -2 | (due = startofday(-2d) and due = startOfWeek(-2d)) or | 5 | 5 | (due = startofday(5d) and due = startOfWeek(5d)) or | -9 | -9 | (due = startofday(-9d) and due = startOfWeek(-9d)) or | -16 | -16 | (due = startofday(-16d) and due = startOfWeek(-16d)) or | |
SAT | -1 | -1 | (due = startofday(-1d) and due = startOfWeek(-1d)) or | 6 | 6 | (due = startofday(6d) and due = startOfWeek(6d)) or | -8 | -8 | (due = startofday(-8d) and due = startOfWeek(-8d)) or | -15 | -15 | (due = startofday(-15d) and due = startOfWeek(-15d)) or | |
SUN | 0 | 0 | (due = startofday(0d) and due = startOfWeek(0d)) or | 7 | 7 | (due = startofday(7d) and due = startOfWeek(7d)) or | -7 | -7 | (due = startofday(-7d) and due = startOfWeek(-7d)) or | -14 | -14 | (due = startofday(-14d) and due = startOfWeek(-14d)) or | |
MON | MON | 0 | 1 | (due = startofday(0d) and due = startOfWeek(1d)) or | 7 | 8 | (due = startofday(7d) and due = startOfWeek(8d)) or | -7 | -6 | (due = startofday(-7d) and due = startOfWeek(-6d)) or | -14 | -13 | (due = startofday(-14d) and due = startOfWeek(-13d)) or |
TUE | 1 | 2 | (due = startofday(1d) and due = startOfWeek(2d)) or | 8 | 9 | (due = startofday(8d) and due = startOfWeek(9d)) or | -6 | -5 | (due = startofday(-6d) and due = startOfWeek(-5d)) or | -13 | -12 | (due = startofday(-13d) and due = startOfWeek(-12d)) or | |
WED | 2 | 3 | (due = startofday(2d) and due = startOfWeek(3d)) or | 9 | 10 | (due = startofday(9d) and due = startOfWeek(10d)) or | -5 | -4 | (due = startofday(-5d) and due = startOfWeek(-4d)) or | -12 | -11 | (due = startofday(-12d) and due = startOfWeek(-11d)) or | |
THU | -4 | -3 | (due = startofday(-4d) and due = startOfWeek(-3d)) or | 3 | 4 | (due = startofday(3d) and due = startOfWeek(4d)) or | -11 | -10 | (due = startofday(-11d) and due = startOfWeek(-10d)) or | -18 | -17 | (due = startofday(-18d) and due = startOfWeek(-17d)) or | |
FRI | -3 | -2 | (due = startofday(-3d) and due = startOfWeek(-2d)) or | 4 | 5 | (due = startofday(4d) and due = startOfWeek(5d)) or | -10 | -9 | (due = startofday(-10d) and due = startOfWeek(-9d)) or | -17 | -16 | (due = startofday(-17d) and due = startOfWeek(-16d)) or | |
SAT | -2 | -1 | (due = startofday(-2d) and due = startOfWeek(-1d)) or | 5 | 6 | (due = startofday(5d) and due = startOfWeek(6d)) or | -9 | -8 | (due = startofday(-9d) and due = startOfWeek(-8d)) or | -16 | -15 | (due = startofday(-16d) and due = startOfWeek(-15d)) or | |
SUN | -1 | 0 | (due = startofday(-1d) and due = startOfWeek(0d)) or | 6 | 7 | (due = startofday(6d) and due = startOfWeek(7d)) or | -8 | -7 | (due = startofday(-8d) and due = startOfWeek(-7d)) or | -15 | -14 | (due = startofday(-15d) and due = startOfWeek(-14d)) or | |
TUE | MON | -1 | 1 | (due = startofday(-1d) and due = startOfWeek(1d)) or | 6 | 8 | (due = startofday(6d) and due = startOfWeek(8d)) or | -8 | -6 | (due = startofday(-8d) and due = startOfWeek(-6d)) or | -15 | -13 | (due = startofday(-15d) and due = startOfWeek(-13d)) or |
TUE | 0 | 2 | (due = startofday(0d) and due = startOfWeek(2d)) or | 7 | 9 | (due = startofday(7d) and due = startOfWeek(9d)) or | -7 | -5 | (due = startofday(-7d) and due = startOfWeek(-5d)) or | -14 | -12 | (due = startofday(-14d) and due = startOfWeek(-12d)) or | |
WED | 1 | 3 | (due = startofday(1d) and due = startOfWeek(3d)) or | 8 | 10 | (due = startofday(8d) and due = startOfWeek(10d)) or | -6 | -4 | (due = startofday(-6d) and due = startOfWeek(-4d)) or | -13 | -11 | (due = startofday(-13d) and due = startOfWeek(-11d)) or | |
THU | -5 | -3 | (due = startofday(-5d) and due = startOfWeek(-3d)) or | 2 | 4 | (due = startofday(2d) and due = startOfWeek(4d)) or | -12 | -10 | (due = startofday(-12d) and due = startOfWeek(-10d)) or | -19 | -17 | (due = startofday(-19d) and due = startOfWeek(-17d)) or | |
FRI | -4 | -2 | (due = startofday(-4d) and due = startOfWeek(-2d)) or | 3 | 5 | (due = startofday(3d) and due = startOfWeek(5d)) or | -11 | -9 | (due = startofday(-11d) and due = startOfWeek(-9d)) or | -18 | -16 | (due = startofday(-18d) and due = startOfWeek(-16d)) or | |
SAT | -3 | -1 | (due = startofday(-3d) and due = startOfWeek(-1d)) or | 4 | 6 | (due = startofday(4d) and due = startOfWeek(6d)) or | -10 | -8 | (due = startofday(-10d) and due = startOfWeek(-8d)) or | -17 | -15 | (due = startofday(-17d) and due = startOfWeek(-15d)) or | |
SUN | -2 | 0 | (due = startofday(-2d) and due = startOfWeek(0d)) or | 5 | 7 | (due = startofday(5d) and due = startOfWeek(7d)) or | -9 | -7 | (due = startofday(-9d) and due = startOfWeek(-7d)) or | -16 | -14 | (due = startofday(-16d) and due = startOfWeek(-14d)) or | |
WED | MON | -2 | 1 | (due = startofday(-2d) and due = startOfWeek(1d)) or | 5 | 8 | (due = startofday(5d) and due = startOfWeek(8d)) or | -9 | -6 | (due = startofday(-9d) and due = startOfWeek(-6d)) or | -16 | -13 | (due = startofday(-16d) and due = startOfWeek(-13d)) or |
TUE | -1 | 2 | (due = startofday(-1d) and due = startOfWeek(2d)) or | 6 | 9 | (due = startofday(6d) and due = startOfWeek(9d)) or | -8 | -5 | (due = startofday(-8d) and due = startOfWeek(-5d)) or | -15 | -12 | (due = startofday(-15d) and due = startOfWeek(-12d)) or | |
WED | 0 | 3 | (due = startofday(0d) and due = startOfWeek(3d)) or | 7 | 10 | (due = startofday(7d) and due = startOfWeek(10d)) or | -7 | -4 | (due = startofday(-7d) and due = startOfWeek(-4d)) or | -14 | -11 | (due = startofday(-14d) and due = startOfWeek(-11d)) or | |
THU | -6 | -3 | (due = startofday(-6d) and due = startOfWeek(-3d)) or | 1 | 4 | (due = startofday(1d) and due = startOfWeek(4d)) or | -13 | -10 | (due = startofday(-13d) and due = startOfWeek(-10d)) or | -20 | -17 | (due = startofday(-20d) and due = startOfWeek(-17d)) or | |
FRI | -5 | -2 | (due = startofday(-5d) and due = startOfWeek(-2d)) or | 2 | 5 | (due = startofday(2d) and due = startOfWeek(5d)) or | -12 | -9 | (due = startofday(-12d) and due = startOfWeek(-9d)) or | -19 | -16 | (due = startofday(-19d) and due = startOfWeek(-16d)) or | |
SAT | -4 | -1 | (due = startofday(-4d) and due = startOfWeek(-1d)) or | 3 | 6 | (due = startofday(3d) and due = startOfWeek(6d)) or | -11 | -8 | (due = startofday(-11d) and due = startOfWeek(-8d)) or | -18 | -15 | (due = startofday(-18d) and due = startOfWeek(-15d)) or | |
SUN | -3 | 0 | (due = startofday(-3d) and due = startOfWeek(0d)) or | 4 | 7 | (due = startofday(4d) and due = startOfWeek(7d)) or | -10 | -7 | (due = startofday(-10d) and due = startOfWeek(-7d)) or | -17 | -14 | (due = startofday(-17d) and due = startOfWeek(-14d)) or | |
THU | MON | 4 | 8 | (due = startofday(4d) and due = startOfWeek(8d)) or | 11 | 15 | (due = startofday(11d) and due = startOfWeek(15d)) or | -3 | 1 | (due = startofday(-3d) and due = startOfWeek(1d)) or | -10 | -6 | (due = startofday(-10d) and due = startOfWeek(-6d)) or |
TUE | 5 | 9 | (due = startofday(5d) and due = startOfWeek(9d)) or | 12 | 16 | (due = startofday(12d) and due = startOfWeek(16d)) or | -2 | 2 | (due = startofday(-2d) and due = startOfWeek(2d)) or | -9 | -5 | (due = startofday(-9d) and due = startOfWeek(-5d)) or | |
WED | 6 | 10 | (due = startofday(6d) and due = startOfWeek(10d)) or | 13 | 17 | (due = startofday(13d) and due = startOfWeek(17d)) or | -1 | 3 | (due = startofday(-1d) and due = startOfWeek(3d)) or | -8 | -4 | (due = startofday(-8d) and due = startOfWeek(-4d)) or | |
THU | 0 | 4 | (due = startofday(0d) and due = startOfWeek(4d)) or | 7 | 11 | (due = startofday(7d) and due = startOfWeek(11d)) or | -7 | -3 | (due = startofday(-7d) and due = startOfWeek(-3d)) or | -14 | -10 | (due = startofday(-14d) and due = startOfWeek(-10d)) or | |
FRI | 1 | 5 | (due = startofday(1d) and due = startOfWeek(5d)) or | 8 | 12 | (due = startofday(8d) and due = startOfWeek(12d)) or | -6 | -2 | (due = startofday(-6d) and due = startOfWeek(-2d)) or | -13 | -9 | (due = startofday(-13d) and due = startOfWeek(-9d)) or | |
SAT | 2 | 6 | (due = startofday(2d) and due = startOfWeek(6d)) or | 9 | 13 | (due = startofday(9d) and due = startOfWeek(13d)) or | -5 | -1 | (due = startofday(-5d) and due = startOfWeek(-1d)) or | -12 | -8 | (due = startofday(-12d) and due = startOfWeek(-8d)) or | |
SUN | 3 | 7 | (due = startofday(3d) and due = startOfWeek(7d)) or | 10 | 14 | (due = startofday(10d) and due = startOfWeek(14d)) or | -4 | 0 | (due = startofday(-4d) and due = startOfWeek(0d)) or | -11 | -7 | (due = startofday(-11d) and due = startOfWeek(-7d)) or | |
FRI | MON | 3 | 8 | (due = startofday(3d) and due = startOfWeek(8d)) or | 10 | 15 | (due = startofday(10d) and due = startOfWeek(15d)) or | -4 | 1 | (due = startofday(-4d) and due = startOfWeek(1d)) or | -11 | -6 | (due = startofday(-11d) and due = startOfWeek(-6d)) or |
TUE | 4 | 9 | (due = startofday(4d) and due = startOfWeek(9d)) or | 11 | 16 | (due = startofday(11d) and due = startOfWeek(16d)) or | -3 | 2 | (due = startofday(-3d) and due = startOfWeek(2d)) or | -10 | -5 | (due = startofday(-10d) and due = startOfWeek(-5d)) or | |
WED | 5 | 10 | (due = startofday(5d) and due = startOfWeek(10d)) or | 12 | 17 | (due = startofday(12d) and due = startOfWeek(17d)) or | -2 | 3 | (due = startofday(-2d) and due = startOfWeek(3d)) or | -9 | -4 | (due = startofday(-9d) and due = startOfWeek(-4d)) or | |
THU | -1 | 4 | (due = startofday(-1d) and due = startOfWeek(4d)) or | 6 | 11 | (due = startofday(6d) and due = startOfWeek(11d)) or | -8 | -3 | (due = startofday(-8d) and due = startOfWeek(-3d)) or | -15 | -10 | (due = startofday(-15d) and due = startOfWeek(-10d)) or | |
FRI | 0 | 5 | (due = startofday(0d) and due = startOfWeek(5d)) or | 7 | 12 | (due = startofday(7d) and due = startOfWeek(12d)) or | -7 | -2 | (due = startofday(-7d) and due = startOfWeek(-2d)) or | -14 | -9 | (due = startofday(-14d) and due = startOfWeek(-9d)) or | |
SAT | 1 | 6 | (due = startofday(1d) and due = startOfWeek(6d)) or | 8 | 13 | (due = startofday(8d) and due = startOfWeek(13d)) or | -6 | -1 | (due = startofday(-6d) and due = startOfWeek(-1d)) or | -13 | -8 | (due = startofday(-13d) and due = startOfWeek(-8d)) or | |
SUN | 2 | 7 | (due = startofday(2d) and due = startOfWeek(7d)) or | 9 | 14 | (due = startofday(9d) and due = startOfWeek(14d)) or | -5 | 0 | (due = startofday(-5d) and due = startOfWeek(0d)) or | -12 | -7 | (due = startofday(-12d) and due = startOfWeek(-7d)) or | |
SAT | MON | 2 | 8 | (due = startofday(2d) and due = startOfWeek(8d)) or | 9 | 15 | (due = startofday(9d) and due = startOfWeek(15d)) or | -5 | 1 | (due = startofday(-5d) and due = startOfWeek(1d)) or | -12 | -6 | (due = startofday(-12d) and due = startOfWeek(-6d)) or |
TUE | 3 | 9 | (due = startofday(3d) and due = startOfWeek(9d)) or | 10 | 16 | (due = startofday(10d) and due = startOfWeek(16d)) or | -4 | 2 | (due = startofday(-4d) and due = startOfWeek(2d)) or | -11 | -5 | (due = startofday(-11d) and due = startOfWeek(-5d)) or | |
WED | 4 | 10 | (due = startofday(4d) and due = startOfWeek(10d)) or | 11 | 17 | (due = startofday(11d) and due = startOfWeek(17d)) or | -3 | 3 | (due = startofday(-3d) and due = startOfWeek(3d)) or | -10 | -4 | (due = startofday(-10d) and due = startOfWeek(-4d)) or | |
THU | -2 | 4 | (due = startofday(-2d) and due = startOfWeek(4d)) or | 5 | 11 | (due = startofday(5d) and due = startOfWeek(11d)) or | -9 | -3 | (due = startofday(-9d) and due = startOfWeek(-3d)) or | -16 | -10 | (due = startofday(-16d) and due = startOfWeek(-10d)) or | |
FRI | -1 | 5 | (due = startofday(-1d) and due = startOfWeek(5d)) or | 6 | 12 | (due = startofday(6d) and due = startOfWeek(12d)) or | -8 | -2 | (due = startofday(-8d) and due = startOfWeek(-2d)) or | -15 | -9 | (due = startofday(-15d) and due = startOfWeek(-9d)) or | |
SAT | 0 | 6 | (due = startofday(0d) and due = startOfWeek(6d)) or | 7 | 13 | (due = startofday(7d) and due = startOfWeek(13d)) or | -7 | -1 | (due = startofday(-7d) and due = startOfWeek(-1d)) or | -14 | -8 | (due = startofday(-14d) and due = startOfWeek(-8d)) or | |
SUN | 1 | 7 | (due = startofday(1d) and due = startOfWeek(7d)) or | 8 | 14 | (due = startofday(8d) and due = startOfWeek(14d)) or | -6 | 0 | (due = startofday(-6d) and due = startOfWeek(0d)) or | -13 | -7 | (due = startofday(-13d) and due = startOfWeek(-7d)) or | |
) | ) | ) | ) | ||||||||||
ORDER BY due ASC, key ASC | ORDER BY due ASC, key ASC | ORDER BY due ASC, key ASC | ORDER BY due ASC, key ASC |
Oh, by the way you need to remove the "or" from the last row - these were generated in Excel and I forgot to update the last row...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.