1 Reply Latest reply: Jan 25, 2017 5:03 AM by Sunny Talwar RSS

    bar chart showing 3 rolling years

    Rhona Corcoran

      Hi I am trying to crete a bar chart with three rolling years in it so if it is march 2016 it will show back to march 2015 etc.

       

      I have it working when I have current year and previous year data but when I put in the third year I the december figure or most recent figure either doubles or is wrong

       

      the dimension is month and the expressions I have are:

       

      Current year:

      =num(Sum({$<CIFCode ={'26.204.18'},ReferredYYYY, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.18'}>}YearMonth), -11))<=$(=Max({<CIFCode ={'26.204.18'}>}YearMonth))"}>}ActualValue)

      /Sum({$<CIFCode ={'26.204.15'},ReferredYYYY, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.15'}>}YearMonth), -11))<=$(=Max({<CIFCode ={'26.204.15'}>}YearMonth))"}>}ActualValue),'##0%')

      Current year - 1:

       

      =num(Sum({$<CIFCode ={'26.204.18'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.18'}>}YearMonth), -24))<=$(=Addmonths(Max({<CIFCode ={'26.204.18'}>}YearMonth),-12))"}>}ActualValue)

      /Sum({$<CIFCode ={'26.204.15'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.15'}>}YearMonth), -24))<=$(=Addmonths(Max({<CIFCode ={'26.204.15'}>}YearMonth),-12))"}>}ActualValue),'##0%')

       

      Current year - 2:

       

      =num(Sum({$<CIFCode ={'26.204.18'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.18'}>}YearMonth), -35))<=$(=Addmonths(Max({<CIFCode ={'26.204.18'}>}YearMonth),-24))"}>}ActualValue)

      /Sum({$<CIFCode ={'26.204.15'},ReferredYYYY=, YearMonth={">=$(=AddMonths(Max({<CIFCode ={'26.204.15'}>}YearMonth), -35))<=$(=Addmonths(Max({<CIFCode ={'26.204.15'}>}YearMonth),-24))"}>}ActualValue),'##0%')

       

      Thanks