see the attached
there were a couple of issues
you were using a filter to "select the end date for your 4 week" - the filter selected a single week - filters filter out all records that don't meet the criteria so you only get the one week
I used a parameter that lets yo still choose a single week - note the set up
then the filter based on the parameter (i used datetrunc)
for the WTD the filter is
for the 4 week rolling it is
then the $ formula needs to be aggregated
it returns this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
4 week rolloing.twbx 31.8 KB
Thanks very much Jim, it worked perfectly. Much appreciated.
Would it be best to use a similar 'datetrunc' calculated field to obtain the YTD amount based on the selected parameter control? e.g. if I selected week 28, it would include all data before and inclusive of week 28?
I was thinking the following however it only works for the most recent week..
[Week Ending]>=datetrunc('year',[week end end date parameter])
[Week Ending]<=[week end end date parameter]
Natalie - I just use datetrunc because it returns a date that can be compared to dates in your data set - others use datediff to determine the number of weeks between 2 dates - its all what makes you comfortable