2 Replies Latest reply: Nov 12, 2010 4:02 PM by Jason Michaelides

# Set Analysis with Variables trouble

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!

Jason

• ###### Set Analysis with Variables trouble

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.

• ###### Set Analysis with Variables trouble

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