I have created an [Netflow % (12 months)] with formula
IF ATTR(DATEDIFF('month',[Data Position],[Parameter : Data_Position])) <= 11
THEN
IF ATTR([Mia Group]) = '180 dpd &+' THEN
SUM([Value]) / (LOOKUP([Previous row],-1) + [Previous row])
ELSE
SUM([Value]) / LOOKUP([Previous row],-1)
END
else 0
END
It show the value when i put in the Date segment. How to display the average without put in the date dimension, just like the example highlighted below.
ATTR tests whether the maximum and minimum values in a partition are the same. If you have the field in your view, i.e. the Month, then the maximum and minimum month (in each month) will always be the same, and your calculation will work. Adding ATTR is often used as the magical fix for aggregation errors, but it's not that magical. As soon as you remove the required field from the worksheet, the maximum and minimum values are different and your condition no longer works, it doesn't go to the underlying data and get a subset of rows to aggregate, to do that you'd have to move your IF into the aggregation itself. That wouldn't help you here, because you're trying to use LOOKUP. You can't LOOKUP previous values for something that isn't in the worksheet (because now there is no previous value), so you can't do what you're trying to do with your data as it is. As far as I can see, you'd have to join your data to itself based on [Month] = DATEADD('month',-1,[Month]), i.e. join January to December, December to November etc.), giving you access to the values in the previous month, and use that as [Previous Row] instead of your LOOKUP.
