I am attempting to create a LY Sales metric so I can graph Sales and LY Sales by Month. My data source has sales by month, where the "Date" field contains the first day of the month (ex. 2/1/2014, 3/1/2014, 4,1/2014, ect)
I have added the following expression for this calculation, but can not get it to return values.
Is the table you provided in the last post the actual source data, or is that your desired result?
Would a Pivot Table chart object do what you need, such as this:
As Jonathan mentioned you can lightly transform the data - using the data load editor - or by creating new dimensions in the master items list. If you create a Year and Month field from your date field it will make the task much easier.
Year(Date) as Year,
Month(Date) as Month,
(html, codepage is 1252, embedded labels, table is @1);
The data I provided was just demonstrative - not the actual source data. It does not contain "LY Amount" as a column. Ultimately what I am trying to calculate is a "Variance to Last Year" metric.
I have created a bar chart that displays by month and year, but my desired output is to have the bar in the chart represent the Variance from the current year to the previous year for that given month.
I have drawn in red boxes on the my current chart to indicate the metric that I would like to display.
OK great thanks - I sent this to a colleague of mine to see if what you want to do is possible. In the mean time, can you send some sample data / mock of data of your source so we may work with it. Or can we use the data you provided such as the date and sales column and just ignore the LY column?