2 Replies Latest reply: Oct 8, 2013 4:41 AM by Peter Drechsler RSS

    Show only last 12 Month with Dimension YearMonth

    Heri Zhang

      Hello all,

       

      I want to show sum of amount in chart (bar,line). Dimension I must use is YearMonth, then the case is when I click 'Jan' and Year '2012', the chart show only amount of last 12 month. Jan-2012, Dec-2011, Nov-2011, ... Feb-2011. Then, if I select Month from Jan-Apil, it will show the amount of last 12 Month from Max Month I select (April). I had try to use dimension limit and max show value, but it can't be like that. I want to set it in expression. Can you help me? Thanks before.

        • Show only last 12 Month with Dimension YearMonth
          Teemu Pitkänen

          Hey Heri,

           

          I think set analysis is the answer for your problem. I would create a sort order number for your YearMonth field. You can then use something like this in your set analysis expression:

           

          Sum( {< YearMonth=, YearMonthOrder={$(v_12_month_set)} >} Amount), where v_12_month_set is a variable having the expressions for your sort order. It should be something like this:

           

          = '"<=' & Max(YearMonthOrder) & ' >=' & Max(YearMonthOrder)-11 & '"'

           

          Hope this helps!

           

          -Teemu

          • Re: Show only last 12 Month with Dimension YearMonth

            Dear Heri,

             

            what I did was creating a variable as end date and from that a starting date for the x-axis:

             

            vEndDate: =today()

            vStartDate: =addmonth(today(), -12)

             

            qv1.bmp

             


            That gives me the last 12 month.

             

            My problem is that I do a rolling 12 month calculation, which gives the correct value for the first month that is shown. But, it doesn't scale the y-axis correctly.