Excluding a set of bookingNumbers from count of ordernumbers

[PhilipZ]

Hello everyone,

I need to count all orderNumbers filtered by a set analysis but in addition to that need to exclude all orderNumbers that are found by filtering the data with different criteria than in the first set analysis.

I simplified the table and current formulas for this request.

We have a pretty large table containing details for each of our individual bookings.

Each booking can be part of an order, basically like a shopping card. But since we are in the tavel business, we have many cases where additional orders are created when extras for a travel are booked or a travel plan is changed (one entry is flagged to be canceled, while another is created with a new bookingDate). This may result in multiple entries per orderNumber where we still only count distinct orderNumbers for a reporting period.

Here is an example how our table structure basically looks:

orderNumber	bookingNumber	bookingDate	touroperator_leistung	dataSource_leistung	price_leistung
111111	1111	01.09.2023	XYZ	offline	1600
22222	1234	02.10.2023	ABC	offline	2000
12345	2345	01.10.2023	XYZ	offline	2100
12345	3456	02.06.2024	ABC	offline	300
23456	4567	03.06.2024	CDE	offline	1500
23456	5678	03.06.2024	ABC	offline	400
99999	6789	03.06.2024	DDD	offline	40

Currently I setup a formula counting our orderNumbers matching several criteria with a set analysis (I simplified it by only adding some of the criteria, since most of them are not needed for this example):
Count(distinct {$*[Current Timeframe]<tourOperator_leistung-={'DDD'},dataSource_leistung={'offline'}>}orderNumber_leistung)

We use the alternate State [Current Timeframe] to filter our reporting with a date-range. We filter by the field bookingDate using that state and setup multiple Alternate States to compare date-ranges in one dashboard.

So filtering the bookingDate for a timeframe of 01.06.2024 to 07.06.2024 and using the current set analysis, we would get the result of two orders (first 2 rows are orders outside the selected date-range and therefore excluded from the count. 4th and 5th row are counted as one as they have the same orderNumber and the last row is excluded due to the set-Analysis filter on the touroperator.

What I want to do in addition:

I want to exclude every order from the above calculation when there was any booking at any time with a specific touroperator (XYZ) at any time, having the same orderNumber as the bookings included in the calculation.

Given the mentioned example and table: I would like to Exclude (not count) the order in Row 4 (orderNumber: 12345) since there was a booking for the tourOperator "XYZ" on the same orderNumber at some other time.
So my result, using the same parameters before but excluding the mentioned booking should actually be one order.

I can't exclude the individual orders in my analysis since there are thousands of orders I possibly need to exclude, so I need the calculation of the orderNumbers in the formula.

I know I can get each orderNumber to be excluded with an Aggregation: 
=Aggr(Only({<[tourOperator_leistung] ={'XYZ'}>}orderNumber_leistung),orderNumber_leistung)
but I don't know if / how it would be possible to implement that in the current formula.

Unfortunately, I can't just create a new table and report from there, since we cross-filter between multiple visualisations in our dashboards and across dashbaords using the same tables.

I could add another field to the table to determine if there is a booking with the specific touroperator with the same ordernumber for each entry, but that would not be ideal in my opionion due to creating many redundant datapoints and enlarging the table for that one calculation, so I hope there is a solution that can do the calculation within the app in a formula.

Thanks,

Philip

[marcus_sommer · Accepted solution]

You could try to add the following as part within the set analysis:

... orderNumber = e({< touroperator = {'XYZ'}>} orderNumber) ....

[PhilipZ]

Hi @marcus_sommer ,

thanks for your reply and sorry for the late response, I was knocked out for a couple days and then flooded with other work.

I tried to get this to work. The issue is, that I can't just use it as part of the current set analysis, since it is filtered on the Alternate State and current selection: "{$*[Current Timeframe]..."

So the orders are only excluded if there is also a booking for this specific touroperator for the selected timeframe in the alternate State [Current Timeframe]. 

So, I need to exclude these orderNumbers from my calculation if the order included a booking for that touroperator at any point in time with any selections (except the exclusions I define in the set analyses itself). And then do the current set analysis for the counting of orders in the defined timeframe / filters on top of that.

I think I have to nest this somehow. I tried nesting it with an Aggr, but it does not seem to work as I intend (it does not exclude all orders).

Any ideas on how to fix this?

Thanks,
Philip

[marcus_sommer]

That there are multiple states involved doesn't mean that the conditions couldn't be included within a set analysis because the states could be part of the conditions - nested and/or multiple set statements are associated to each other. For the how to I suggest you look into the help because I have not much practical experience with merging multiple states.

Beside of this I suggest to re-think the usage of alternate states because many of wanted views will be possible without it and the efforts and complexity of this feature could become quite high.

[PhilipZ]

Hi @marcus_sommer ,

thanks again.

Unfortunately I can't just get rid of the alternate states since most of our Dashboards make heavy use of them to display / compare several KPI for two or more timeframes.

So I would need to overhaul on how we setup our tables and every dashboard, which is not achievable for me in a reasonable time.

However, I am happy to say, that I finally found the solution for my request, using your Answer with a simple adaption:

Like you said, nesting different states can be done and was actually not the issue in my case.

But I needed to adjust the scope for the nested Set analysis in the E() Function by setting the correct identifier "1" to include the full set of order IDs independent of the selection in the outer set analysis.

I also found an explanation for this syntax here: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

• An inner set expression with a set identifier will NOT inherit from the context. It will inherit the selection from the set identifier instead.
• An inner set expression that lacks set identifier – it has only a set modifier – will inherit from the context.

So the correct solution works like this:

Count(distinct {$*[Current Timeframe]<orderNumber_leistung = E({1< touroperator = {'XYZ'}>} orderNumber_leistung),tourOperator_leistung-={'DDD'},dataSource_leistung={'offline'}>}orderNumber_leistung)

Thanks again, case closed.

Philip