You'll first need to create a date parameter (or 2 if you want a range). Then, create a calculated field similar to the following:
if [Date Field]<=DATEADD('year', -1, [date parameter]) then [Date Field] end
Then, place this on the filter shelf and select True. Right click on the parameter and select Show Parameter Control.
Hope this helps!
Thanks for your reply. I have only gotten access to the historical sales today so I am only looking at this now.
I have to admit I am utterly confused and unable to achieve what I need.
To confirm, I am trying to show sales for example department by store. The user selects a date range (e.g. 1st Nov 13 to 30th Nov 13) and the report will show sales for this date range AND also the sales for the date range minus 1 year (i.e. 1st Nov 12 to 30th Nov 12).
I have attached a spread sheet which might better show the layout I am trying to achieve.
Thanks for your assistance so far.
Sales Comparison.xls 22.0 KB
thanks for your help. I managed to get this working... thanks again.
Hi Tracy and John,
I am trying to do the same thing, with sales for a date range compared year over year. Specifically, I am trying to do sales month to date compared year over year, but am not able to get it to work. Can you please provide more detailed instructions or attach the workbook?
Thank you very much.
I can't post up the workbook where I resolved the YOY sales comparison but I will detail the steps I used to achieve same. Hopefully you can work it out from these...
1) Created Parameter START DATE (Date Type = Date, Display Format = Automatic, Allowable values = All )
2) Created Parameter END DATE (properties as above)
3) Created Calculated Field SALES TP (this period) with formula:
IF [Start Date] <= [DocDate] and [DocDate] <= [END DATE] THEN [SALESIncVat]
Change [DocDate] to be your date field and [SalesIncVat] to be your sales field
4) Created Calculated Field SALES LP (last period) with formula:
IF [Start Date] <= DATEADD('year', 1,[DocDate]) AND
DATEADD('year',1,[DocDate]) <= [End Date]
then [Sales Inc Vat] END
Again change fields to be your fields.
If you are interested, I then created a Sales Diff (value) and a Sales Diff (%) also... (hope this doesn't look like I am showing off but as this is my first deployment I was chuffed to get these working!!)
5) Created a Calculated Field SALES +/- with formula:
ZN(SUM([Sales TP])) - LOOKUP(ZN(SUM([Sales LP])), 0)
6) Created a Calculated Field SALES +/- % with formula:
(ZN(SUM([Sales TP])) - LOOKUP(ZN(SUM([Sales LP])), 0)) / ZN(SUM([Sales LP]))
A screenshot of a worksheet using these values below shows layout, filters etc.
Hope this works for you...
Thank you so much – it worked!! Only problem is I need to do this for many measures that are in rows:
I can create the necessary calculated fields for each measure, but am not sure how to get the layout to look like above.
Great to have help from overseas! (I had to look up ‘chuffed’). ☺
image001.png 7.3 KB