Hey Guys,
I am facing a curious problem.
I need to show the sum of the ledger fact data with some contract (dimension) data aggregated:
For example a part of the raw data looks like (first two are contract data, the last two are ledger fact data:
Contract number, Number of active contracts, Booking value, Booking ledger id
1111111, 1, 100, A
1111111, 1, 200, B
1111111, 1, 300, C
Report should show only two columns:
Number of active contracts, Booking value
1, 600
I have set both data items the Aggregate Function to Total and the Rollup Aggregate Funcito to Automatic.
The ledger data is fine and the dimension shows 3 instead of one.
Number of active contracts, Booking value
3, 600
I guessed it is not problem lets divide by the number of the ledger id based on contracts and we will get the right results.
I have tried this one:
[Number of active contracts] / count([Booking ledger id] for [Contract number])
But the result was wrong: 0,33333
If I used this [Number of active contracts] / 3 then I got the right results but I don't want to hardcoded it (sometimes you have only two ledger id).
If I run seperately the count([Booking ledger id] for [Contract number]) then I get the result 3, so I do not understand why I get wrong results when I am using the calculated field for divisor.
Any Idea?
Thanks and best,
P
I think you're getting confused with detail data vs aggregate data, and that's throwing your numbers off.
a) Number of active contracts (detail) = 1
b) count([count([Booking ledger id] for [Contract number]) = 3
Therefore, a / b = 0.333 which is correct. You shouldn't have to resort to this type of calculation just so you can get the aggregate.
Number of active contracts should have an aggregation of none, as it doesn't sound like this should be an aggregate field.
Aggregate and Rollup for Booking Value should be set to Total.
Without knowing much about the tables and joins involved it's not possible to give much insight.