Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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))