Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Specialist
Partner - Specialist

Set Analysis version of an IF statement

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

10 Replies
Not applicable

Try this:

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


hope this hleps


MC

alexis
Partner - Specialist
Partner - Specialist
Author

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

aeolosIssue.jpg

qlikpahadi07
Specialist
Specialist

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
Partner - Specialist II
Partner - Specialist II

try,


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

Not applicable

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

Maybe

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikpahadi07
Specialist
Specialist

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Champion
Partner - Champion

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