1 Reply Latest reply: Feb 8, 2017 3:14 AM by Marcus Sommer RSS

    Show previous week that starts on a Friday - Pivot table

    Martha Parsons

      Hello, I have a pivot table and they only want to see the previous week.


      Previously my dimension was this:

      =Date(WeekStart(StartDate,4, 0),'DD-MMM-YY') &  chr(10) & Date(WeekEnd(StartDate, 4, 0),'DD-MMM-YY')

      and this worked great. Until they decided to only show the previous week only.


      And it has to start on Friday. So, being that today is the 3rd, it would actually show the January 27 - February 2nd, If today was February 1st, it would need to show January 20th - January 26th (since the all of February 1 data would not be in).


      I tried the following and none work:

      =Date(WeekStart(StartDate,4, 0)-7,'DD-MMM-YY') &  chr(10) & Date(WeekEnd(StartDate, 4, 0)-7,'DD-MMM-YY')


      =Date(WeekStart((Today() - (7)), 4, 0),'DD-MMM-YY') &  chr(10) & Date(WeekEnd((Today() - (7)), 4, 0),'DD-MMM-YY')


      My expression is:  Count([business])


      How do I just show the previous week so no matter what is selected it is always that? Do I use a set expression in the dimension? And how would I do that?



      Thank you so much!