Set Analysis with Variables trouble

[Jason_Michaelides]

Hi all,

I'm sure I have come close to solving this with the help of existing forum posts, but I can't quite seem to get it working. After 2 solid days of trying I'm calling out for help!!

I have two tables:

Table1 - Show_ID, ShowYear, ShowName, ShowStartDate

Table2 - Show_ID, OrderDate, SaleValue

These tables are obviously linked on Show_ID (unique for each show in each year).

I have a variable vDaysToShow -

=IF(IsNull(Only(Show_ID)),'<Select 1 Show>', (ShowStartDate-Today()))

What I'm trying to achieve is this - When you select a ShowName and ShowYear (unique Show_ID) I want to show total sales for the selected year vs sales at this point last year, i.e. ShowStartDate - vDaysToShow

E.g. Selected ShowName is ClothesShowLive, selected ShowYear is 2010. This year it starts on 03/12/2010 - 22 days from now. Last year it started 04/12/2009. I need to see the total sales so far for this year (easy -

SUM(SaleValue)

), but I also want to see total sales for 22 days before the ShowStartDate, last year.

I've tried all sorts...

I can get the total sales for last year easily enough:

=SUM( {$<ShowYear = {$(#=Only(ShowYear)-1)}>} SaleValue )

and I can hard-code the date limit:

=SUM( {$<ShowYear = {$(#=Only(ShowYear)-1)}, OrderDate = {"<=12/11/2009"}>} SaleValue )

I need the OrderDate bit above to be <= StartDateLastYear - vDaysToShow, basically! So I started by creating a variable vStartDateLastYear:

=DATE(ONLY( {$<ShowYear = {$(#=Only(ShowYear)-1)}>} StartDate),'DD/MM/YYYY')

and then tried loads of ways of getting that into the equation e.g.

=SUM( {$<Show Year = {$(#=Only(ShowYear)-1)}, OrderDate = {(vStartDateLastYear - vDaysToShow)}>} SaleValue])

but just can't get it to work!

Please help!

Jason

[pover · Accepted solution]

The variables also need a dollar sign expansion and don't remember having much luck nesting $($()) so I would combine the 2 variables into one do the following:

OrderDate = {"<=$(vStartDateLastYear_vDaysToShow)"}>}

Regards.

[Jason_Michaelides]

Karl - you are an absolute legend. Thank you mate - worked perfectly! Final expression looks like this:

=SUM( {$<[Show Year] = {$(#=Only([Show Year])-1)}, [Order Date] = {"<$(vTodayLastYear)"}>} [Sale Value] )

(Happy) Jason