Compare period this year with same period last year.

[lawrenceiow]

I need to compare recent performance against the same period in recent years.

My FactsTable contains Location, Date, Quantity and I want to compare the last 7 days with the same 7 day period last year. I also need to compare a 28 day window and a 90 day window. I will also need to be able to compare these periods matching day to day. By this I mean if I compare yesterday 24-Jul-2014, which is Thursday, I need to compare with 25-Jul-2013 as this is Thursday also (actually, I will also need to compare with 26-Jul-2012, etc).

For Example:

Location	19-07-2013 to 25/07/2013	18-07-2014 to 24/072014	Variance
East	19	24	-20.8
West	55	42	31.0
North	70	54	29.6
South	23	23	0

At first I thought I'd use Week(Date) but, of course, that is fixed weeks, ie each week always starts on the same day of the week.

Looking at the Date and Time Functions help page I wouldn't be surprised if there's something in there to help me but struggling to find which is the right one.

[#2 · Accepted solution]

Hi,

There are two ways to get this, the first is usisng an expression with SetAnalysis and the second way is using Alternate State.

With SetAnalysis you have to select a period (19--07-2014 to 25-07-2014) and the result last year is exactly the same but -1 year (19-07-2013 to 25-07-2013)  in this case you need to expression 1 for actual period and for the last year period

Actual: Sum({$<Year={$(#=Max(Year))}>}Sales)

or in your case specify the same week and same year

Sum({$<Year={$(#=Max(Year))}, Week{$(#=Max(Week))}>}Sales)

Last Year -1:

Sum({$<Year={$(#=Max(Year)-1)}>}Sales)

or in your case specify the same week and year -1

Sum({$<Year={$(#=Max(Year)-1)}, Week{$(#=Max(Week))}>}Sales)

In case use Alternate State you have to create 1 Alternate State in properties of document and give it a name (MyLastPeriod), then create two list boxes with field Year and two list boxes with field Week, then take one Year list box and one Week list box and go to properties and in General Sheet and select the name of your alternate state that you created (MyLastPeriod), finally in your object add two expressions:

Actual: Sum(Sales) // It works with selections of list boxes without Alternate State

Last Year -1: Sum({MyLastPeriod} Sales) // It works with selections of list boxes with Alternate State

Best regards

[lawrenceiow]

Thanks for your reply Oswaldo Vasquez, I'm just finishing work for the day so I'll try out your suggestion on Monday.  At a quick glance I'm slightly worried as you are using Week() and I didn't have much luck with this. However, I'll trust you (for now ) and see what happens when I try it out on Monday.

Regards

[lawrenceiow]

Thanks Oswaldo Vasquez, that works really well. I went for the Alternate State version you mentioned (to be honest I'd completely forgotten about alternate states) as this provides me with a lot more flexibility around the two periods being compared (and I can add another period if I make another alternate state and another collection of alternate state listboxes).