3 Replies Latest reply: Mar 26, 2013 1:16 PM by Gysbert Wassenaar RSS

    Chart showing previous 6 months

    Danielle van Eeden

      Hi All,

       

      I am trying to put together a line chart showing the average cost per unit for the previous 6 months, depending on the date selections made in the calendar.

       

      The dimension of the chart is Cal_MonthYear (which is date(monthstart(Date), 'MM YYYY'). So if the user selects Feb-13 in the calendar, I would want the chart to show Sep-12, Oct-12, Nov-12, Dec-12, Jan-13, Feb-13.

       

      I have tried to put together an expression along the lines of the following, but I can't get it to work;

       

      =(sum(Cost) / sum(units))

       

      +

       

      (sum({<Cal_MonthYear = {"$(=date(addmonths(Cal_MonthYear, -1), 'MM YYYY'))"}>}Cost)

      /

      (sum({<Cal_MonthYear = {"$(=date(addmonths(Cal_MonthYear, -1), 'MM YYYY'))"}>}Units)

       

      +

       

      (sum({<Cal_MonthYear = {"$(=date(addmonths(Cal_MonthYear, -2), 'MM YYYY'))"}>}Cost)

      /

      (sum({<Cal_MonthYear = {"$(=date(addmonths(Cal_MonthYear, -2), 'MM YYYY'))"}>}Units)

       

      etc.......

       

      I'm assuming it must be a relatively straight-forward thing to do, but I just can't seem to get it to work.

       

      Any help greatly appreciated!

       

      Thanks,

       

      Danielle

        • Re: Chart showing previous 6 months
          Gysbert Wassenaar

          You can't use a calculated dimension in set analysis expressions. The set is calculated before the chart, so the calculated dimension doesn't exist yet then.

           

          Perhaps this does what you want:

          =(sum({<Date={'>=$(=addmonths(monthstart(max(Date)),-6))<$(=monthstart(max(Date)))'} >} Cost) / sum({<Date={'>=$(=addmonths(monthstart(max(Date)),-6))<$(=monthstart(max(Date)))'} >} Units))

           

          If not, it would help if you posted the document you're working on or a representative example.

            • Re: Chart showing previous 6 months
              Danielle van Eeden

              Hi Gysbert,

               

              I don't think I worded my question very well - what I meant was that the dimension Cal_MonthYear is calculated in the script as date(monthstart(Date), 'MM YYYY'). It's not a calculated dimension.

               

              I have tried your above suggestion but can't get it to work. When you state Date, do you mean my date field (i.e. Cal_MonthYear)?

               

              Thanks for your help,

               

              Danielle

                • Re: Chart showing previous 6 months
                  Gysbert Wassenaar

                  Ah, ok. Yeah, in that case use Cal_MonthYear instead of Date. monthstart won't be needed either since you already used that in the script.

                   

                  =(sum({<Cal_MonthYear={'>$(=addmonths(only(Cal_MonthYear),-6))<=$(=only(Cal_MonthYear))'} >} Cost) / sum({<Cal_MonthYear={'>$(=addmonths(only(Cal_MonthYear),-6))<=$(=only(Cal_MonthYear))'} >} Units))