Maybe you could use something like
=sum(aggr(if(MONTH =$(#varPriorMonth), FORECAST_VALUE)), MONTH)
Maybe a need a total or missed a bracket, not tested yet 😉
you probaly have more than one FORECAST per month,
=sum(aggr(sum(if(MONTH =$(#varPriorMonth), FORECAST_VALUE))), MONTH)
should be better?
edited by swuehl
Hi, I think it might be easier to use chart inter-record functions to accomplish this task, like the above funtion. Similiar to the following:
Notice the rangesum function is used to return either the value of the previous month or zero, when the column is january.
Above() works as long as the chart remains sorted in a specific order and you don't need a previous month for the very first month in the chart. If you need to get around these limitations, you can also search the forum for 'AsOf' for examples of handling this sort of thing with data. Briefly:
AsOfMonth, MonthType, Month
August 2011, Current, August 2011
August 2011, Previous, July 2011
July 2011, Current, July 2011
July 2011, Previous, June 2011
Use AsOfMonth instead of Month in the chart and make MonthType a new chart dimension. Now you only need one expression instead of two.
Edit: This approach also has weaknesses, primary I think that you now have two month fields instead of one, which can cause confusion when making selections. If you select July 2011 in the Month field, for instance, this chart will show both August and July, with the same numbers, and August will have the numbers in the previous month column, while July will have it in the current month column. It's correct, but probably not what you intended to look at when you selected that month.
That was a good application u uploaded,
Could anyone tell me the significance of the code i have highlighted in red, as there is no value after "=" sign...
Thanks MS. I'm glad it was helpful. I've uploaded a couple of others too - just need to tidy up some more before they go up also.
Thanks also Tammy for fielding the 'ignore' query.