Excluding Weekends from Reports: A How-To Guide

Yael P.

Hi All - is there a way i can excludes weekends on my reports? e.g track events only on week days

15 comments

Andrew S.

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.

Andrew S.

The simpler formula is just

(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7

Which gives you a 0-6 value

Yael P.
·
·
Thank you! Andrew S. Somehow it still shows the data on days i didn’t pick, any chance you know why?
Andrew S.
·
·
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).

Andrew S.

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")

Andrew S.
·
·
see if I limit my date to one day (my way of testing), it shows just the one day
Andrew S.
·
·
My simpler formula shows this way
Yael P.
·
·
Got it! I used the more complexes formula and have two quick questions
1.
Are you using as an event / filter / breakdown?
2.
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
Andrew S.
·
·
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.
Andrew S.
·
·
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"
Andrew S.
·
·
this show week days filtered (so 5 days) as a metric
Andrew S.
·
·
or a pie
Andrew S.
·
·
or the bar
Andrew S.
·
·
Line and Column are the two "over time axis" insights
Yael P.
·
·
Okay. Thank you so much! This is super helpful 🙏