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

# Chart showing previous 6 months

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

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:

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

• ###### Re: Chart showing previous 6 months

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)?