Set analysis for Same "Weekday" Last four Weeks

[jahncersantana]

Hi,

I am having some issues constructing a set analysis, to count only volume that occurred on the same day of the week as today, for the past for weeks.

For example, if today is Monday, count only Mondays for the past 4 weeks, giving me something like this.

Today (lets say is Monday) Volume = 50
Last 4 Mondays avg 45

[bhargav_bhat]

Hi Jahncersantana,

You need to follow below steps

1. Create a master calendar which contains  Weekday, Week , and Year and associate it with date column
2. Now in Set Analysis write below expression

       count ( { <Weekday={$(=weekday(today(1)))} , Week={">=$(=max(Week)-3)"} ,Year={$(=max(Year))}  > }                                  Quantity)

Regards,

Bhargav

[zzyjordan · Accepted solution]

Hi,
You can create a weekday field in your load script like: Weekday([Your date field]) as weekday,
and then you the [weekday] in the below set analysis expression for last 4 weekday average:

=Avg({$<[your date field]={">=$(=Date(Today()-28))<$(=Today())"},weekday={$(=Weekday(Today()))}>}[your measure])

Hope this helps
ZZ

[jahncersantana]

Perfect, Thanks!!!