Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this:
=sum({$<WeekBeginningSunday=P({<TourOperatorId={'394'}>}ArrivalsWeekBeginning)>} Excursions)
hope this hleps
MC
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...
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)
try,
sum({<WeekBeginningSunday={'=(WeekBeginningSunday=ArrivalsWeekBeginning)'}, [TourOperatorId]={394}>} Excursions)
sum({$<TourOperatorID={'394'},p({1<WeekBeginningSunday=ArrivalsWeekBeginning}>}TourOperatorID)>}Excursions)
Maybe
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
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!!!!
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
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