topic Re: Data Aggregation Problem in App Development

Data Aggregation Problem

cgT — Wed, 03 Jul 2024 10:10:15 GMT

Hi experts,

I'm having a problem with some data based around capacities in an auditorium.

I have a fixed capacity figure (pulled through from a spreadsheet, I'm using QS Enterprise) which is broken down into each level of the auditorium. Here is an example:

Performance Date & Time	Capacity	Part of House
29/04/24 19:30	713	Stalls
29/04/24 19:30	442	Dress Circle
29/04/24 19:30	372	Grand Circle

These are pulling through correctly. However, when I take out the 'Part of House' dimension, it appears to aggregate this numbers, like so:

Performance Date & Time	Capacity
29/04/24 19:30	550.4589178

I was expecting this numbers to sum together, creating a total of 1,527. It appears that some sort of an aggregation is being applied, as though the 1,527 total is being divided by the number of Parts of House, however this doesn't add up either, as multiplying the 550.4589178 by 3 gives 1,651.38.

Is anyone able to help me decipher what's going on please?

Many thanks,

Re: Data Aggregation Problem

cgT — Wed, 03 Jul 2024 12:03:37 GMT

And to add clarity, this expression is currently calculating the capacity down to the PoH level:

sum(Capacity)/count([Performance Date & Time])

Re: Data Aggregation Problem

Fabiano_Martino_Intelco — Wed, 03 Jul 2024 12:25:44 GMT

Hi @cgT

that's odd.

If you create two separate expressions:

- sum(Capacity)

- count([Performance Date & Time])

what values do you get?

I suggest you test these expressions in both tables, to check if some values are not aligned.

Regards

Re: Data Aggregation Problem

cgT — Wed, 03 Jul 2024 12:52:22 GMT

Hi @Fabi ,

Here is what I get if I remove the expression and just drop in the Capacity field, along with the count of PerfDate&Time:

Performance Date & Time	Capacity	Capacity	count([Performance Date & Time])
29/04/24 19:30	550	549,358	998

Re: Data Aggregation Problem

TcnCunha_M — Wed, 03 Jul 2024 13:08:22 GMT

Sum(Aggr(Sum(Capacity), Performance Date & Time, Part of House))

Re: Data Aggregation Problem

cgT — Wed, 03 Jul 2024 13:11:25 GMT

This gives me a value of 1,815,599...

Re: Data Aggregation Problem

TcnCunha_M — Wed, 03 Jul 2024 13:13:00 GMT

so there some duplication on your data, try o add distinct
Sum( distinct Aggr(Sum(Capacity), Performance Date & Time, Part of House))

Re: Data Aggregation Problem

cgT — Wed, 03 Jul 2024 13:14:47 GMT

The figure of 1,815,599 still remains...

Re: Data Aggregation Problem

TcnCunha_M — Wed, 03 Jul 2024 13:18:56 GMT

are there any Part of House Null ?

because check if check " suppress when value is null", might be is courting now those values where if you have suppress null values doesn't count
because the aggregation should solve your problem, i believe there something on data model

Re: Data Aggregation Problem

TcnCunha_M — Wed, 03 Jul 2024 13:20:36 GMT

See this sample i did it

Re: Data Aggregation Problem

Fabiano_Martino_Intelco — Wed, 03 Jul 2024 13:25:07 GMT

HI,

checking your results you have a capacity of 549,358 and a date count of 998

The division gives exactly 550.4589178 which is your result.

I suggest you check why you have 998 dates and not 3 as expected.

The advice of @TcnCunha_M is correct, start from the data model and see if you have "strange" links between tables or duplicate values.

Regards

Re: Data Aggregation Problem

cgT — Thu, 04 Jul 2024 07:28:03 GMT

Thank you both for your suggestions.

Here is an example of a line of my data (anonymised):

Booking Reference

ShowName

Venue

Capacity

Tickets

Gross

PoH

Profile

Profile Group

Discount

Price Band

TransactionDate

PerformanceDateTime

TransactionHour

2340560

Mickey Mouse

The Mouse House

442

£882

Dress Circle

GROUPS (DMGS)

Groups Sales

GRPS Mon-Thu 10+

11/04/2024

29/04/2024 19:30:00

Each sales record consists of this data. 'Capacity' refers to the total number of seats in the Part of House ( PoH - in this case the Dress Circle). This capacity figure loads in for each record which is why I divide it by the number of performances (determined by counting the number of individual Performance Date Time entires).

I can't find a way of building in a capacity value for the venue itself, broken down by level because then I wouldn't need to fudge it using a calculation in-app.

I'm hoping you'll be able to spot what may be wrong...

Many thanks again

Re: Data Aggregation Problem

TcnCunha_M — Thu, 04 Jul 2024 07:45:57 GMT

Does the capacity change when new buy happen ?

I.e., les assume someone by a 10 new tickets, so the new record will have capacity 430 and tickets 10 ?

Re: Data Aggregation Problem

cgT — Thu, 04 Jul 2024 08:15:51 GMT

The capacity of the venue is fixed, so, in using your example, the record will always show the capacity as the same number as well as listing the number of tickets sold as 10.

I hope that makes sense?

Re: Data Aggregation Problem

Fabiano_Martino_Intelco — Thu, 04 Jul 2024 08:43:50 GMT

Hi, this could be the cause of your problem.

If you have multiple rows with the same data (PerformanceDateTime and Capacity) the aggregation functions (Sum and Count) will consider them as multiple values.

In this scenario you can use the DSITINCT clause in the function or process in the script the table and extract with DISTINCT the data you need to create a separate table with each value included in a single row.

Regards

Re: Data Aggregation Problem

cgT — Thu, 04 Jul 2024 08:50:57 GMT

So my load script looks like this:

Sales:
LOAD
"Booking Reference",
Show,
Venue,
Capacity,
Tickets,
Gross,
PoHTrim AS PoH,
Profile,
"Profile Group",
Discount,
"Price Band",
PerformanceDateTime AS [Performance Date & Time],
TransactionDate AS [Transaction Date & Time], 
TransactionHour

Would you be able to give me an example of how to load the Capacity value using the Distinct function? Would it look something like this:

sum(DISTINCT Capacity) AS Capacity,

Thanks again

Re: Data Aggregation Problem

Fabiano_Martino_Intelco — Thu, 04 Jul 2024 09:02:00 GMT

Hi,

I would create a separate table:

CapacityTable:
LOAD Distinct
Capacity as PoHCapacity,
PoH,
[Performance Date & Time] as PoHPerformanceDataAndTime
Resident Sales;

Note that I renamed the fields except for the PoH, that is the key between the tables.

Since I don't know your data model the key might be different, but this table should be a good starting point.

Regards

Re: Data Aggregation Problem

cgT — Thu, 04 Jul 2024 12:28:31 GMT

Thanks so much for this @Fabiano_Martino_Intelco ,

I've tweaked your script suggestion and used a Composite Key across the PoH, Venue and Show fields to get it to work correctly.

Your suggestion very much inspired me though, so thank you for your help.