Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I try to replace Year with max(year). However, the month is not showing anything except Month= {11}>}. And I need to add up 3-months value. Please advice.
Here is what I have now (SUM ({$<Year={2016},Month={11}>}(Cash)))+(SUM ({$<Year={2016},Month={10}>}(Cash)))+(SUM ({$<Year={2016},Month={9}>}(Cash))). I want to change to a form of sum({$<Year = {$(=max(Year))}, Month= {$(=max(Month))}>} Cash)
You could try
=Sum( {$<Year={2016},Month={">=$(=Max(Month)-2)"}>} Cash)
But it would be easier if you could make the set analysis selection on a Year-Month or Date field, or a consecutive MonthID field, just to handle the year change correctly.
See also
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync
I think this is probably not a good way to do this because what if you are in Month 2 of 2017, how do you imagine going to get 12 2016 in your expression? I suggest using Dates instead of Year and Month separate.
Look here: