Hello,
I simply want to apply a „rule of three“ equation across two tables. I cannot find out how to do.
The table 1 contains the columns:
[diagnose], [age], [gender], [number of patients]
There are 165.000 lines combined by 8.000 different diagnosis, 19 age groups and 2 gender
The table 2 contains the columns:
[zip code], [age], [gender], [number of inhabitants]
There are 315.000 lines combined by 8.200 different [zip code], 19 [age group] and 2 [gender]
I know want do calculate, how many patients can be expected in a selected combination of [diagnose], [age group] and [gender] for a selected combination of [zip code].
For each combination of [diagnosis] and [zip code] I calculate:
[number of patients]
divided by the total sum of [number of inhabitants]
multiplied by the sum of [number of inhabitants] in the selected [zip code]
Before aggregating the calculation has to be done on the level of detail [age group] and [gender].
The problems:
A join of both tables will result in 1.246.400.000 lines. Therefore, it is not possible and I have to do the equation across two data sources. I thought LOD-Expression could be a way. For example:
{include [age group], [gender]: sum ([number patients])}
divided by
{fixed [age group], [gender]: sum(number of inhabitants)}
multiplied by
{include [age group], [gender]: sum([number of inhabitants])}
As I found out, LOD expression across two data sources are not possible and I found no way to avoid this. Also by applying data blending, I found no way to achieve the desired results.
Can someone help me or is it simply not possible?
Dirk
Hi Jonathan,
thank you for reply. We have built a comparable solution in Microsoft Analysis Services and QlikView. In both solutions, it is easy to do. However, we are looking for better visualization and flexible and intuitive frontends for our customers. In this case, Tableau seems for us to be the best solution.
I build some examples where you can see what I am looking for in Excel. Unfortunately, I was not able to upload my Tableau workbook file. Therefore, I put it in my dropbox. You can download the files by the following link.
https://dl.dropboxusercontent.com/u/59422973/diagnosis_inhabitants.zip
There are three basic views.
- Diagnosis placed on rows and measures on columns (example 1). Filter for regions based on zip-codes. Nice to have: Also, filter for age and gender.
- Regions placed on rows and measures on columns (example 2). Filter for diagnosis. Nice to have: Also, filter for age and gender.
A
- plot on a geographical map for a selection of diagnosis, age and gender (example 3).
The maximum number of different diagnosis to be shown in a table will be about 400. Normally far less about 20-30.
To get valid estimations of the number of patients, which could be expected, the calculation has to be made on an LOD of [age group] and [gender] before aggregation.
Dirk
- Diagnosis placed on rows and measures on columns (example 1). Filter for regions based on zip-codes. Nice to have: Also, filter for age and gender.
