Hello everyone!
I have another problem and would appreciate any input you guys could give me
We have several subsidiaries, which generate revenue at different hours in a day. The raw table looks something like this:
| Sub | BonNo | Hour | Rev | Date |
|---|
| F1 | 1 | 1 | 1 | 01.01.12 |
| F1 | 2 | 1 | 2 | 01.01.12 |
| F1 | 3 | 2 | 3 | 01.01.12 |
| F1 | 4 | 2 | 4 | 01.01.12 |
| F1 | 5 | 3 | 5 | 01.01.12 |
| F1 | 6 | 3 | 6 | 01.01.12 |
| F2 | 1 | 2 | 1 | 01.01.12 |
| F2 | 2 | 2 | 2 | 01.01.12 |
| F2 | 3 | 2 | 3 | 02.01.12 |
| F2 | 4 | 2 | 4 | 02.01.12 |
| F2 | 5 | 2 | 5 | 03.01.12 |
| F2 | 6 | 3 | 6 | 03.01.12 |
I have created a table, which shows revenue per hour per subsidiary and looks like this:
| Hour | Sub | Count | Sum of Rev |
|---|
| 1 | F1 | 1 | 3 |
| 2 | F1 | 1 | 7 |
| 2 | F2 | 3 | 15 |
| 3 | F1 | 1 | 11 |
| 3 | F2 | 1 | 6 |
Dimensions: Hour, Sub
Formulas: Count = count(distinct total<Hour,Sub> Datum), Sum of Rev = sum(Rev)
What I would actually like to have is the above table, but with the Sub dimension removed and the count summed additionally over that dimension instead, like this:
| Hour | Count | Sum of Rev
|
|---|
| 1 | 1 | 3 |
| 2 | 4 | 22 |
| 3 | 2 | 17 |
This should be Revenue per Subsidiary-Hour (or whatever you want to call it)
Sum of Rev = sum(count(distinct total <Hour,Sub> Datum) doesn't work
I am sure there is a way, but I don't see it right now
Regards,
Sandro
