Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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')
and
=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!
Maybe it's better to create these date-dimensions within the script. Here you will find a good starting point for this:
- Marcus