• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 88 views
  • Users 0 members are here
Related

Analytics- Attendance date, by recurrence pattern (e.g. every third Sunday of the month)

Alison Lambert
$organization

Hi all, 

I'd like to find an easy way in Analytics to be able to view every third Sunday of the month's numbers, compared to other Sundays. I know I can do this using Day of Week Sort to select only Sundays, and then Days in Date and tick all the specific Sunday dates, but that relies on me remembering to go in and tick the new date every week after the date has occurred (as you can't select future dates with that filter). 

I'm sure I found a way to do something similar to this in a prior organisation, but can't figure it out. Does anyone know if there's a filter for this or have found a good solution? 

Thanks!

Alison 

Parents
  • $organization

    Hi Alison,

    One idea I have for you is a formula to rank the Sundays within each month, and then filter to just the rows where that rank is 3. We can't just use Calendar Week of Month = 3 in combination with Day of Week = Sunday because sometimes the first Sunday is in Week 0 and other times Week 1. With a RANK() function we can make the first Sunday always 1, and then limit the results to the 3rd.

    RANK ( [Min Calendar Week of Month] , "ASC" , "1234" , [Calendar Month] )

    That puts a rank value on every row of the results, ranking each row based on its [Min Calendar Week of Month], ascending, without repeated rankings, and restarting the ranking at every change in [Calendar Month]. You can find the RANK() function in the Functions list, and mouse over it for a more complete description.

    Setting that value as a filter = 3, disabling the RANK in the widget, and adding Ticket Count, I get this.

    Then a tried it without the value rank filter. I filtered on Days in Date, with a Ranking type filter, set to return the top 12 (1 Sunday for each month of 1 year)

    IF( 
      RANK( [Min Calendar Week of Month] , "ASC" , "1234", [ Calendar Month] ) = 3
      , 1 , NULL 
    )

    And with that same filter, an indicator widget of just Total Ticket Count.

    ThirdSundays.dash

Reply
  • $organization

    Hi Alison,

    One idea I have for you is a formula to rank the Sundays within each month, and then filter to just the rows where that rank is 3. We can't just use Calendar Week of Month = 3 in combination with Day of Week = Sunday because sometimes the first Sunday is in Week 0 and other times Week 1. With a RANK() function we can make the first Sunday always 1, and then limit the results to the 3rd.

    RANK ( [Min Calendar Week of Month] , "ASC" , "1234" , [Calendar Month] )

    That puts a rank value on every row of the results, ranking each row based on its [Min Calendar Week of Month], ascending, without repeated rankings, and restarting the ranking at every change in [Calendar Month]. You can find the RANK() function in the Functions list, and mouse over it for a more complete description.

    Setting that value as a filter = 3, disabling the RANK in the widget, and adding Ticket Count, I get this.

    Then a tried it without the value rank filter. I filtered on Days in Date, with a Ranking type filter, set to return the top 12 (1 Sunday for each month of 1 year)

    IF( 
      RANK( [Min Calendar Week of Month] , "ASC" , "1234", [ Calendar Month] ) = 3
      , 1 , NULL 
    )

    And with that same filter, an indicator widget of just Total Ticket Count.

    ThirdSundays.dash

Children