Calculate client share for a region

[dirk_konings]

Hi i have following data :

Client	Region	WorkedHours
A	1	10
A	1	10
A	2	10
A	2	10
A	1	10
B	3	10
B	3	10
B	3	10
C	1	10
C	1	10
C	2	10
C	2	10

When loaded into QV, i want to get this result :

Region	WorkedHours	Distinct Clients	Correct # Clients
1	50	2	1,1
2	40	2	0,9
3	30	1	1
Total	120	5	3

How do i calculate the column 'Correct # Clients'?

Client A has a share of worked hours in region 1 and 2.

0,6 in Region 1 and 0,4 in Region 2

And the 3 in the total is the correct number of distincted clients over all regions.

Thanks

Dirk konings

[#2 · Accepted solution]

Hi Dirk,

You could create a pivot table with Region as dimension and the following as expressions

Worked Hours:

Sum (WorkedHours)

Dinstinct Clients:

Sum(aggr(TextCount (DISTINCT Client),Region))

Correct # Clients:

Sum(aggr(sum(WorkedHours)/sum(Total <Client> WorkedHours),Region,Client))

You should enable Partial sums for Region in your pivot table.

I am attaching an example based on your data.

Do let me know if this is what you are looking for.

Nimish