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

    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 -

      ), 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!