Rolling 12 Month Chart

[rwb139]

Hi all,

I know that this question has been asked 100 times, but I couldn't figure out a solution that would work for me. I want to create a rolling 12 month chart that to measure our net_operating_income. I am using this formula in my table to designate whether or not the date is within the 12 months:

if(((year(today(2))*12)+month(today(2))) - (((year([rem_snapshot.report_date])*12)+month([rem_snapshot.report_date])))<12,1,0)

I have tried set analysis to sum({<rem_rolling_12_month={1}>}net_operating_income) but it doesn't work correctly. Any help would be amazing. Thanks!

[QFabian]

Hi @rwb139  , try using variables inside your set analysis, here an example :

sum({<Year = , Month= , Period = {">=$(vPeriod11)<=$(vPeriodo1)"} >} amount)

vPeriod1
=num(monthend(max(Period)))

vPeriodo12
=(num(addmonths(Periodo,-12)))

[rwb139]

Hi @QFabian 

I have gotten it to work by adding a field to my data set that just says the number of months ago. Then using set analysis I am only using the fields with a months_ago field of less than 12: avg({<report_date=, rent_roll_months_ago={"$(='<'&12)"}>} rent)

Now the final piece of the puzzle is to make it show the month names instead of the number of months ago as displayed. (I only have 6 months in here which is why it's not at 11) .

This chart has my dimension of rent_roll_months_ago and two measures: 

avg({<report_date=, rent_roll_months_ago={"$(='<'&12)"}>} market_rent)

and

avg({<report_date=, rent_roll_months_ago={"$(='<'&12)"}>} rent)

Any ideas on how to change the numbers to month names?

[QFabian]

Sure @rwb139 , you can create another field like this :

Load

your fields,

rent_roll_months_ago,

month(makedate(2020, rent_roll_months_ago))  as Month,

rest of your fields

from [your source];

Then use the new field as dimension.

[rwb139]

@QFabian 

Thanks for your help. I used that expression but it just assigns the months ago to the month it is on the actual calendar. For instance 5 months ago should be September and 4 should be October instead of May and Apr 

[rwb139 · Accepted solution]

Hi @QFabian 

Definitely not the most elegant solution but I got it to work with this expression for my measures 

=if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],0)))', avg({<report_date=, rent_roll_months_ago={'0'}>} rent)
,if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],-1)))', avg({<report_date=, rent_roll_months_ago={'1'}>} rent)
,if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],-2)))', avg({<report_date=, rent_roll_months_ago={'2'}>} rent)
,if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],-3)))', avg({<report_date=, rent_roll_months_ago={'3'}>} rent)
,if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],-4)))', avg({<report_date=, rent_roll_months_ago={'4'}>} rent)
,if(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))') = '$(=Month(AddMonths([report_date],-5)))', avg({<report_date=, rent_roll_months_ago={'5'}>} rent)
))))))

Now my chart is looking good, but would like my most recent month on the right and move back to the left 

I've tried sorting by expression with this expression and then numerically, ascending but it's not working: 

=If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=$(vCurrentMonth))',0
,If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=Month(AddMonths([report_date],-1)))',1
,If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=Month(AddMonths([report_date],-2)))',2
,If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=Month(AddMonths([report_date],-3)))',3
,If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=Month(AddMonths([report_date],-4)))',4
,If(ValueList('$(=$(vCurrentMonth))'
,'$(=Month(AddMonths([report_date],-1)))'
,'$(=Month(AddMonths([report_date],-2)))'
,'$(=Month(AddMonths([report_date],-3)))'
,'$(=Month(AddMonths([report_date],-4)))'
,'$(=Month(AddMonths([report_date],-5)))')='$(=Month(AddMonths([report_date],-5)))',5
))))))

Thanks again and hope this isn't information overload ha

[rwb139]

@QFabian 

Got it working by sorting the expression in descending order. Thanks for your help!