1 Reply Latest reply on Apr 27, 2012 4:59 AM by Jonathan Drummey

    Trying to build a custom calculation that dynamically updates YTD growth % inc/decrease (based on filter criteria) for use as a color indicator in a bar chart

    Michael Sigg

      I have a bar chart that is currently summarizing our top 15 products by Net Sales and Discount % (height of bar indicates Net Sales, width/size of bar indicates discount %).  Discount % is a calculated field.  I'd like to add a calculated field, '% Growth' (vs same period prior year), to the Color Shelf so the color of the bars also tell a story.  I can't find any custom calculations that will allow me to do this.  I have a data table w/ each individual row assigned to a 'Date' that is forced to be the 15th of the respective month, and the data goes back to 2010.  I have a filter set up so I can change my date range - and I will pull in either 1 month or multiple months (for a YTD view) in a given year. 

      When I select, for example, Jan/Feb/&March 2012, I want the bar chart to show Net Sales for Jan/Feb/&March, calculate the discount % for Jan/Feb/March, and then calculate % Growth vs Jan/Feb/Mar 2011.  Or, if I select October 2011, I want the data to show sales/discounts for October 2011 and the color to represent % Growth vs October 2010.

      Is there a way to write a custom calculation that will accomplish this?   I have seen a lot about table calculations, but in this case I'm only showing the sales for the filtered dates so there is no prior period to reference.

      The % Growth calculation will be used in other tabs as well - so it is not only for the bar chart.

      Thanks!

      Mike