I think the problem with your original expression is that you are trying to aggregate to get the max date in the dimension. This will always fail. Some aggregations can be done, if you use the Aggr function.
The potential quick fix to what you have is to create a variable that returns the date six months prior to your maximum date.
To do this create a variable with the name vSixMonthsAgo and give it this code:
=addmonths(max({1}DATE), -6)
This will calculate the max date outside of any dimensions or selections (if you want within the last six months or the latest date in the current selection remove the {1}).
Your expression would then read as:
=if( DATE > vSixMonthsAgo, WeekStart( DATE ))
I would strongly advise to do as much work as possible in your load script though, this will make your life much easier. You can take one source field and map it into many variations or derived fields, eg:
LOAD
DATE,
WeekStart(DATE) as [Start Of Week],
if(DATE >= addmonths(today(), -6), 1, 0) as InLastSixMonths,
[... rest of load ...]
Your dimension could then user
=if(InLastSixMonths = 1, [Start Of Week])
However, this will be calculating values for older dates, just not displaying them. So, better still, you could just use [Start Of Week] as the dimension, and have an expression like this:
sum(Sales * InLastSixMonths)
Note that the load uses today rather than Max(DATE) due to not being able to mix aggregation levels. If you require it to be based against the max DATE you would either need to do a sub query (if your data is coming from SQL) or do a separate load statement with a group by and Peek out the max value to a variable (look up Peek in the help if you need details on this).
To fix the set analysis route outlined above you could put a Date function in the query:
Sum({<Date = {'>=$(=Date(AddMonths(Max(Date),-6),'DD/MM/YYYY HH:MM:SS'))'}>}Sales)
Sorry there is a whole bunch of options in the above, but hopefully by looking through the various approaches you can arrive at the best solution.
- Steve
