topic Re: Set Analysis version of an IF statement in QlikView

Set Analysis version of an IF statement

alexis — Sun, 16 Mar 2014 21:12:48 GMT

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

Re: Set Analysis version of an IF statement

Sun, 16 Mar 2014 21:34:35 GMT

Try this:

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

hope this hleps

Re: Set Analysis version of an IF statement

alexis — Sun, 16 Mar 2014 23:07:36 GMT

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...

Re: Set Analysis version of an IF statement

qlikpahadi07 — Mon, 17 Mar 2014 06:55:52 GMT

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)

Re: Set Analysis version of an IF statement

manojkulkarni — Mon, 17 Mar 2014 07:04:52 GMT

try,

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

Re: Set Analysis version of an IF statement

Mon, 17 Mar 2014 07:21:42 GMT

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

Maybe

Re: Set Analysis version of an IF statement

jonathandienst — Mon, 17 Mar 2014 07:55:26 GMT

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

Re: Set Analysis version of an IF statement

qlikpahadi07 — Mon, 17 Mar 2014 08:02:28 GMT

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

Re: Set Analysis version of an IF statement

alexis — Mon, 17 Mar 2014 09:12:30 GMT

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

Re: Set Analysis version of an IF statement

CELAMBARASAN — Mon, 17 Mar 2014 09:28:37 GMT

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

Re: Set Analysis version of an IF statement

alexis — Mon, 17 Mar 2014 10:42:59 GMT

Hi Celambarasan,

In a nutshell, there are Arrivals every week (on a Sunday) for one or more (usually ALL) resortids.

Likewise, there are ExcursionReservation records most days for one or more resorts so your assumption cannot hold.

All I want is to create a table that shows (for every "weekbeginning" in Arrivals):

o Arrivals (in total or by resort)

o Excursions (in total or by resort)

As I mentioned earlier, I have achieved a solution (for Excursions) using:

= sum(if(WeekBeginningSunday = Arr_WeekBeginning,

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

so all I want is to use Set Analysis instead.

Thanks

Alexis