Using heatmap to show % of each Y category over the entire X category

[veni]

Hello! I am relatively new to Spotfire and I am exploring the usage of the heatmap on a sample dataset I have that consists of: gender, agegroup, and survived ("1" = Survived, "0"= Did not survive).

Now, I want to use the heatmap to represent the percentage of Survived="1" for each age group category as a whole of the gender category. I have added the age group and gender category into the heatmap, I am currently stuck on the aggregation portion to retrieve the percentage of those who survived in each age group with respect to their own gender category.

Would appreciate if someone would like to share their insights to me regarding this, as right now I have been trying to wrap my brain around it. Cheers!

[veni]

Thinking about it, I feel like that might be a little complicated, an alternative is where it just shows the percentage of survival based on their own categories. For example,  if I have 10 records for Female Kid, and 7 survived, so the % of survival would be 70%, which is to be represented in the heatmap for Female Kid

[David Boot-Olazabal]

Hi Veni,

When using your dataset, I get blank spaces for female kids, male teenagers and female senior citizen's.
So I assume your dataset is a bit bigger than what is posted.

Nonetheless, if I understood your first request properly, you want to present the number of survived age groups, as a percentage of the total set of data.
And your visualization needs to be a heatmap?

Kind regards,

David

[Olivier Keugue Tadaa]

Hi Veni,

You can try this expression :::: sum([Survived]) / sum([Survived]) OVER (All([Axis.Y])) since all age groups are displayed on the Y-Axis of your heat map. Ensure you change the expression if you change the settings of your heat map.
 

Here is an example attached 

Let me know if it helps

 

Heatmap example.dxp

[veni]

36 minutes ago, David Boot-Olazabal said:

Hi Veni,

When using your dataset, I get blank spaces for female kids, male teenagers and female senior citizen's.
So I assume your dataset is a bit bigger than what is posted.

Nonetheless, if I understood your first request properly, you want to present the number of survived age groups, as a percentage of the total set of data.
And your visualization needs to be a heatmap?

Kind regards,

David

Hi David,

Yeah that picture is just a snippet of my entire dataset of 800+ records. As for the representation, I decided that I want to present the number of those who survived within their own age group and gender. 

As for the visualization, I was just exploring the heatmap, are there other visualizations within Spotfire that you would recommend me to use?

Regards,

Veni

[Alain Martens]

Hi Veni, 

I agree with David that perhaps another visual would be more appropriate. How about a bar chart? 
Attached you'll find an example on how I approached the problem. I've created 3 calculated columns first to calculate the survival percentage per group. 

Calculated Column 1: 
//Nr Survived Per Group
Sum([Survived]) over (Intersect([Sex],[AgeGroup]))

Calculated Column 2: 
//Nr Per Group
Count(RowId()) OVER (Intersect([Sex],[AgeGroup]))

Calculated Column 3: 
//Survivor Percentage Per Group
[Nr Survived Per Group] / [Nr Per Group]




Hope this helps.

Thanks,

Alain

titanic.dxp

[veni]

13 minutes ago, Olivier Keugue Tadaa said:

Hi Veni,

You can try this expression :::: sum([Survived]) / sum([Survived]) OVER (All([Axis.Y])) since all age groups are displayed on the Y-Axis of your heat map. Ensure you change the expression if you change the settings of your heat map.
 

Here is an example attached 

Let me know if it helps

 

Heatmap example.dxp 468.63 kB · 0 downloads

I've checked out the formula you've used in the .dxp file, for some reason when I transfer it over to mine it gives this error

[Olivier Keugue Tadaa]

If your column Survived is not an integer then you can try sum(Integer([Survived])) / sum(Integer([Survived])) OVER (All([Axis.Y])) to convert it

[veni]

Hi Alain,

I agree that a bar chart would be better

However, I am still open to suggestions on using a heatmap

Regards,
Veni

[veni]

11 minutes ago, Olivier Keugue Tadaa said:

If your column Survived is not an integer then you can try sum(Integer([Survived])) / sum(Integer([Survived])) OVER (All([Axis.Y])) to convert it

Hi Oliver, during the meantime I've also done the visualization using a bar chart, and it doesn't really tally up.

The way I've binned the age is as follows:

case  
when [Age]<=12 then "Kid" 
when [Age]<=18 then "Teenager" 
when [Age]<=30 then "Young Adult" 
when [Age]<=50 then "Adult"
ELSE "Senior Citizen"
end

Missing age values would be replaced with the median age.
This is what the survival rate of Female Kids should look like:

However, when I use the code you've suggested to me it gives:

[Olivier Keugue Tadaa]

That's normal. The row count (on the bar chart) is not the sum of survivals (on the heat map)

Edited June 19 by Olivier Keugue Tadaa

[veni]

During the meantime, I've done some google search and someone came up with this heatmap in Python:

Not sure if I could do this in spotfire, however

[veni]

Just now, Olivier Keugue Tadaa said:

That's normal. The row count (in the bar chart) is not the sum of survivals (in the heat map)

Oh I see, in this case would it be possible to have the percentage of its own category instead of the sum of all the survivals?

[Olivier Keugue Tadaa]

Sure :::: sum(Integer([Survived])) / count([PassengerId]) as [%of Survived]
And you will obtain your Googled image 🙂

Edited June 19 by Olivier Keugue Tadaa

[veni]

5 minutes ago, Olivier Keugue Tadaa said:

Sure :::: sum(Integer([Survived])) / count([PassengerId]) as [%of Survived]

Thank you so much, this solved my issue.