Sum of Counts...

Anonymous — Mon, 14 Jan 2013 14:33:53 GMT

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

Re: Sum of Counts...

CELAMBARASAN — Mon, 14 Jan 2013 14:37:23 GMT

Try the below expression

Count(DIstinct Hour&Sub&Datum)

Hope it helps

Celambarasan

Re: Sum of Counts...

Anonymous — Mon, 14 Jan 2013 15:56:42 GMT

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

topic Sum of Counts... in QlikView

Sum of Counts...

Re: Sum of Counts...

Re: Sum of Counts...