Data Aggregation Problem

[cgT]

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,

[cgT]

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

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

[Fabiano_Martino_Intelco]

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

[cgT]

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

[TcnCunha_M]

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

[cgT]

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

[TcnCunha_M]

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

[cgT]

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

[TcnCunha_M]

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 

[TcnCunha_M]

See this sample i did it