Chart showing previous 6 months

[danielle_v]

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

[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.

[danielle_v]

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

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