forums

Use these forums to ask questions and discuss Tableau.

Ratio calculations when the denominator is to include counts of total records (including nulls)

Posted May 5th, 2008 by overmyhead
in Calculations

I am trying to develop a cost per person ratio by various cost categories, where the number of persons used in the denominator is not necessarily linked to the cost data.

A simplified example:
Person Table includes 4 records with the personid (1,2,3,4) and an exposure count (1,1,1,1)
Cost Table includes 4 records with the personid (1,1,2,2) the type of cost (A,B,A,B) and the cost amount ($400,$600,$800,$1000)

I have linked cost table to person table on personid with a right join to Person Table

I have create a calculated field CostPerPerson= sum(cost amount)/sum(exposure count)

So, for type of cost A, my total cost is $1200 (400+800) and for type of cost B $1600 (600+1000);
My CostPerPerson field calculates as $600=(1200/(1+1)) and $800 =(1600/(1+1))

Instead of the denominator=2, I want it to be the total person exposure of 4= (1+1+1+1)

In terms of this example, Personid 3 and 4 do not purchase anything, but need to be included in the cost per person measure.

My real world application includes many dimensions on both the person table and the cost table. In my cost per person calculation, I want the denominator to be the total exposure by the person table dimensions.

Any suggestions?

Thanks

Add new comment

Comments

On May 5th, 2008 Richard Leeke said:

If I understand this correctly I think you are having the same underlying issue I was the other week - see "How to combine expressions with different levels of aggregation" in this forum.

It sounds as if you want sum(cost_amount) to be aggregated over whatever cost table dimensions you are filtering/analysing by (in this example type of cost), whilst you want sum(exposure_count) to be aggregated over the applicable person table dimensions.

The only way I can see you could achieve that is by arranging for your query to have null (or zero) cost entries for each person for each possible value of each cost table dimension you want to analyse by, so that the extra people get included in the denominator.

You could conceptually do that either by physically adding null/zero rows to the cost table - in your example this would be personid (3,3,4,4), type of cost (A,B,A,B), amount (0,0,0,0) - or possibly with some clever SQL - maybe outer joins or cross-tabs or some such.

Hope that helps - I have spent hours wrestling with the same problem. Post back your solution if you get it going...

reply
On May 6th, 2008 overmyhead said:

Thanks for the help.

I actually have two problems. My example was overly simplistic.

My exposures are not always 1 -- I am dealing with exposure years which can be .5, .75, ... So, when I group across products, my exposures in the denominator get double-counted. For example, if person 1 buys both A&B products and I aggregate across product my cost per person is (A$+B$)/(exposure 1 +exposure 1...) and since I am dealing with fractional exposure I can't replace with a DCOUNT on the person id.

Perhaps if I could solve the above problem, then I could add the null/zero rows to the cost table. Unfortunately, I am running out of time on this project, so I will just slam through using SQL .

When I have more time, I will revisit and if I come up with anything I will post here.

reply