2 Replies Latest reply: Jan 14, 2013 11:11 AM by Sandro Krumbein

# Sum of Counts...

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:

SubBonNoHourRevDate
F1111

01.01.12

F121201.01.12
F132301.01.12
F142401.01.12
F153501.01.12
F163601.01.12
F212101.01.12
F222201.01.12
F232302.01.12
F242402.01.12
F252503.01.12
F263603.01.12

I have created a table, which shows revenue per hour per subsidiary and looks like this:

HourSubCountSum of Rev
1F11

3

2F11

7

2F2315
3F1111
3F216

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:

HourCountSum of Rev
113
2422
3217

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

• ###### Re: Sum of Counts...

Try the below expression

Count(DIstinct Hour&Sub&Datum)

Hope it helps

Celambarasan

• ###### Re: Sum of Counts...

It works! :-) But from my first impression this seems to be highly detrimental to the performance (the actual tables are apparently way bigger than the ones I gave you).

Is there another way to do this? Or is the solution to this to create that as an extra column during the load phase?

Sandro