Set Analysis version of an IF statement

[alexis]

Hi all,

I have created an expression:

= sum(if(WeekBeginningSunday = ArrivalWeekBeginning,

  if([TourOperatorId]='394', Excursions)))

which works fine.

I then try to use SET ANALYSIS to achieve the same (see below) and I am not getting the same results - any ideas?

=sum({$<WeekBeginningSunday=P(ArrivalsWeekBeginning), [TourOperatorId]={'394'}>} Excursions)

Thanks in advance

Alexis

[#2]

Try this:

=sum({$<WeekBeginningSunday=P({<TourOperatorId={'394'}>}ArrivalsWeekBeginning)>} Excursions)

hope this hleps

MC

[alexis]

Thank you Mario for your suggestion.

Unfortunately it returned a zero result.

The below diagram shows you a part of the data structure and explanation...

[qlikpahadi07]

Dear Alexis,

I might haven't correctly Understood your problem but to convert the above expression in Set analysis why you need p().

can't you simply write :

sum({$<WeekBeginningSunday={'ArrivalsWeekBeginning'}, [TourOperatorId]={394}>} Excursions)

[manojkulkarni]

try,

sum({<WeekBeginningSunday={'=(WeekBeginningSunday=ArrivalsWeekBeginning)'}, [TourOperatorId]={394}>} Excursions)

[#6]

sum({$<TourOperatorID={'394'},p({1<WeekBeginningSunday=ArrivalsWeekBeginning}>}TourOperatorID)>}Excursions)

Maybe

[jonathandienst]

Hi

This looks like an expression from a chart/table and you want the condition to be evaluated on a row by row basis. You cannot use set analysis for this purpose as it is evaluated before the chart/table is constructed.

The expression you attempted is not the same as the row by row if statement and will not work anyway unless you select a single value of WeekBeginningSunday.

The recommended way to address this is to create a flag in the load script, set to 1 where WeekBeginningSunday is equal to ArrivalsWeekBeginning, and then use the expression:

     =sum({$<WeekBeginningFlag={1}, [TourOperatorId]={'394'}>} Excursions)

If you do not want to, or cannot change the load script, then you are stuck with the sum(if()), I am afraid.

HTH

Jonathan

[qlikpahadi07]

Dear Alexis,

Pardon, I missed you are matching the Column Data.

Solution for this is exactly what Jonathan have given you.

You need to make a flag in Load script itself

Load *,

....

if(WeekBeginningSunday = ArrivalsWeekBeginning,1) as Flag

then in Front end 

sum({$<Flag={1}, [TourOperatorId]={'394'}>} Excursions)

Cheers!!!!

[alexis]

Thanks for the replies that suggest a flag.

I feel that I have misled you slightly, so let me explain a little more and perhaps you'll see why the flag approach will not work.

In the data structure above the table Arrivals has data of the form:

Arr_WeekBeginning     TUITResortId      ArrivalsPax   

01/09/2013                    1                    123

01/09/2013                    2                    201

01/09/2013                    3                    199

15/09/2013                    1                    133

15/09/2013                    2                    198

15/09/2013                    3                    200

etc...

Please note that this table has dates that are always a SUNDAY!

The ExcursionsReservations table has daily data (not just Sundays) and it's date field - the BookingDate - is linked to a Calendar table. The Calendar table has all the usual fields that we expect from such a table (Day, Month, Dayof the week etc) as well as a column that represent the preceding Sunday (WeekBeginningSunday). So for example, Calendar contains data such as:

BookingDate     WeekBeginningSunday

30/08/2013             25/08/2013

31/08/2013             25/08/2013

01/09/2013             01/09/2013

02/09/2013             01/09/2013

03/09/2013             01/09/2013

04/09/2013             01/09/2013

.....

08/09/2013             08/09/2013

09/09/2013             08/09/2013

......

14/09/2013             08/09/2013

15/09/2013             15/09/2013

16/09/2013             15/09/2013

....

So for EVERY entry in ExcursionsBooking we have a "WeekBeginningSunday" value. The idea/requirement is to match the data in ExcursionsBooking with the data in arrivals to produce a table 

WeekBeginning     ArrivalsPax (from Arrivals)     ExcursionPax (from ExcursionsReservations)

01/09/2013               523                                   ???

15/09/2013               531                                   ???

As you can see the flag approach is not the solution here...

Hope this helps to better explain my issue.

Thanks again

Alexis

[CELAMBARASAN]

Check with the below expression

=sum({$<ResortId ={"=WeekBeginningSunday=ArrivalsWeekBeginning"}, [TourOperatorId]={'394'}>} Excursions)

Assuming that one ResortId will have only one WeekBeginningSunday and one ArrivalsWeekBeginning.

If not you have to form a key combination to achieve this.

Regards

Celambarasan