Set Analysis with Variables trouble

Jason_Michaelides — Fri, 12 Nov 2010 12:39:05 GMT

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

Set Analysis with Variables trouble

pover — Fri, 12 Nov 2010 16:25:37 GMT

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

Jason_Michaelides — Fri, 12 Nov 2010 21:02:54 GMT

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

topic Set Analysis with Variables trouble in QlikView

Set Analysis with Variables trouble

Set Analysis with Variables trouble

Set Analysis with Variables trouble