4 of 4 people found this helpful
Here's one way to do it. You can create a calculated field that looks like this:
STR(DATE(DATETRUNC('week', [Order Date])))
+ " to " +
STR(DATE(DATETRUNC('week', DATEADD('week', 1, [Order Date]))) -1)
DATETRUNC returns the first day of the week (or any date part you specify within the function). That means, it alone can give us the beginning of the week. For the end of the week, we can use DATETRUNC and DATEADD to get the first day of next week and go back one day.
Hope this helps!
Thank you! This gets me part way there!
At first I though it would perfectly, but I realized that because it is making them into a string, it is now sorting my groupings alphabetically. I can get it to sort properly by putting both this calculated field and my date field by week in the column shelf. I just have to figure out if I can switch the header locations, or do a workaround in with same dashboard tricks.
Two more questions:
1) my dates are stored as datetime20 (from SAS), so they also have a time component. It is therefore displaying the dates with time, for example "Aug 21 2016 0:00:00 to Aug 27 2016 0:00:00." Do you know how to get rid of the time portion?
2) Is there a way to set the date format? So instead of "Aug 21 2016" it could be "08/21/2016" ?
I tried to solve both of these issues by adding STR(DATEPARSE('mm/dd/yyyy', ....)) to each line, but the DATEPARSE statement is resulting in an error "Oracle database error 1858: ORA-01858: a non-numeric character was found where a numeric was expected"
I also tried breaking down the calculation into three separate calculated fields (one for each DATE function, and a third to combine then back together using STR) and setting the default date format to mm/dd/yyyy but it still displays as "Aug 21 2016 0:00:00".