Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following code:
=num(Sum({$<CIFCode ={'26.204.13'},ReferredYYYY, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.13'}>}YearMonth), -12))<=$(=Max({<CIFCode ={'26.204.13'}>}YearMonth))"}>}ActualValue)
/Sum({$<CIFCode ={'26.204.10'},ReferredYYYY, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.10'}>}YearMonth), -12))<=$(=Max({<CIFCode ={'26.204.10'}>}YearMonth))"}>}ActualValue),'##0%')
This shows the data for last 12 months in a line chart but I need to have another dimension in the chart to show the previous rolling 12 months so if it is october 2016 the expression above will show from October 2015 to October 2016 and the next expression should show from October 2014 to October 2015.
Any help on how I can do this would be appreciated. When I put in ReferredYYYY-1 I just get the two expressions on the one line in the chart.
Thanks
Try this,
=num(Sum({$<CIFCode ={'26.204.13'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.13'}>}YearMonth), -24))<=$(=Addmonths(Max({<CIFCode ={'26.204.13'}>}YearMonth),-12))"}>}ActualValue)
/Sum({$<CIFCode ={'26.204.10'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.10'}>}YearMonth), -24))<=$(=Addmonths(Max({<CIFCode ={'26.204.10'}>}YearMonth),-12))"}>}ActualValue),'##0%')
Regards,
Kaushik Solanki
you need to adapt yearmonth as well and give a range from -24 to -13 for your second expression
and ReferredYYYY-1 will give you the previous year? ReferredYYY should not be necessary as you restrict with yearmonth?