Excluding Weekends from Reports: A How-To Guide
Hi All - is there a way i can excludes weekends on my reports? e.g track events only on week days
Hi All - is there a way i can excludes weekends on my reports? e.g track events only on week days
I wrote this formula before which I use to get the days of the week in words. Using the custom properties.
IFS((FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=0,"Sunday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=1,"Monday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=2,"Tuesday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=3,"Wednesday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=4,"Thursday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=5,"Friday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=6,"Saturday")You can then use that property in your breakdowns and filters.
The simpler formula is just
(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7Which gives you a 0-6 value
Not sure I can tell specifically from that photo but in general, what I find is that on line charts, it won't show it as a broken line but goes from X to 0 to Y . Also, sometimes I find that with the formula I get a person who "crosses the line" so if I narrow my time period to only look at say last Monday, it may show people on Monday and a little on Tuesday bc they crossed the date in their time zone (started at 23:59 and ended at 0:05).
This is a "more complicated" formula I added which seems to do better with not having the cross-overs.
Ifs(LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=0,"Sunday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=1,"Monday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=2,"Tuesday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=3,"Wednesday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=4,"Thursday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=5,"Friday",LET(STRING_DATE, STRING(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"datetime","value":"$time"}"""@),
NUMBER(MID(STRING_DATE,9,2)) +
FLOOR((2.6 *
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,6,2)) - 2,
NUMBER(MID(STRING_DATE,6,2)) + 10
)
) - 0.2
) -
(2 * NUMBER(LEFT(STRING_DATE,2))) +
IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) +
FLOOR(IF(NUMBER(MID(STRING_DATE,6,2)) > 2,
NUMBER(MID(STRING_DATE,3,2)),
NUMBER(MID(STRING_DATE,3,2)) - 1
) / 4
) +
FLOOR(NUMBER(LEFT(STRING_DATE,2)) / 4)
) % 7=6,"Saturday")
Got it! I used the more complexes formula and have two quick questions
Are you using as an event / filter / breakdown?
Where you able to exclude the days you didn’t pick? It doesn’t count the days i didn’t pick but it still shows them as 0
I personally tend to use it as a breakdown vs a filter. But as a filter, you can set it to only show those days you want. I think the difference though is I tend to use this as a bar or pie or metric as opposed to a line. with a line, the axis is every day, so the date that is a "Saturday" (filtered out) will show 0 bc that date is there but you filtered out showing anything that is = Saturday. With a Bar (like my image above), you only see bars for what is included. So if you put M-F, you would have only 5 bars, not 7. Or if a pie, you'd only have 5 slices. Or a metric, you'd only have 5 values.
You can see in this comparison if I only show the Friday day, how it peaks every 7 days (on Friday) in the line chart. But the bar and pie and matrix show just the Friday since it does not have to show "over time"
Line and Column are the two "over time axis" insights
Okay. Thank you so much! This is super helpful 🙏