cancel
Showing results for 
Search instead for 
Did you mean: 

How do you make a scatter plot from this excel file about cancer rate and age?

Mis
8 - Cloud Apps
8 - Cloud Apps

Hi all, I am quite new to data viz and having a hard time to adopt. I want to make a scatter plot from the following incidental rate of cancer, depends on the age and part (e.g. throat, lung)
Unfortunately i could not attach the file at the moment, I don't know if it helps but following is the part of data set I am using. 

As you can see, the ages are listed horizontal, parts and rate per age are listed vertical. I am not sure how I can change into handy data to enable scatter plot.
On Tableau I could somehow use pivot to summarize the number but not on current Sisense so I am assuming that I have to do it on excel.

Any advice is appreciated!

 

Code Part ICD-10 Sex Year Rought Rate 0-4歳 5-9歳 10-14歳 15-19歳 20-24歳 25-29歳 30-34歳 35-39歳 40-44歳 45-49歳 50-54歳 55-59歳 60-64歳 65-69歳
1 All Part C00-C96 Total 2016 784.0 20.2 10.3 10.8 16.1 26.2 44.9 80.6 137.2 226.4 339.9 498.4 731.7 1086.3 1541.6
1 All Part C00-C96 Total 2017 771.4 20.3 10.6 12.3 15.2 24.5 43.2 77.2 132.8 221.1 337.2 470.5 693.9 1048.7 1514.1
1 All Part C00-C96 Total 2018 775.7 18.5 10.4 12.2 16.2 24.4 44.7 79.4 130.3 217.2 331.5 466.7 689.1 1028.9 1494.8
1 All Part C00-C96 Male 2016 917.3 22.4 11.8 11.4 16.3 23.3 32.7 48.2 76.2 123.1 197.6 396.3 746.1 1308.8 2036.7
1 All Part C00-C96 Male 2017 906.4 21.4 11.8 12.8 13.7 21.3 30.6 45.4 72.1 119.3 201.6 370.6 702.9 1262.5 2009.1
1 All Part C00-C96 Male 2018 908.3 20.4 10.7 12.8 15.7 20.6 32.5 50.2 73.5 119.0 197.2 357.0 693.0 1228.5 1973.9
1 All Part C00-C96 Female 2016 657.5 18.0 8.7 10.1 15.8 29.3 57.7 114.0 200.0 332.5 485.0 601.3 717.3 870.2 1077.5
1 All Part C00-C96 Female 2017 643.4 19.1 9.3 11.8 16.7 27.9 56.3 110.1 195.2 325.6 475.5 571.5 685.0 840.9 1050.4
1 All Part C00-C96 Female 2018 650.1 16.5 10.0 11.6 16.6 28.4 57.6 109.7 188.7 318.1 468.6 577.6 685.2 834.6 1045.8
3 口腔・咽頭 C00-C14 Total 2016 17.0 0.0 0.0 0.1 0.4 1.0 1.8 2.5 3.1 5.4 8.4 14.8 21.8 29.7 35.9
3 口腔・咽頭 C00-C14 Total 2017 17.4 0.1 0.1 0.2 0.4 0.9 1.8 2.8 3.3 5.5 8.4 13.1 21.2 30.2 36.9
2 REPLIES 2

HamzaJ
9 - Travel Pro
9 - Travel Pro

Hi @Mis ,

I think you need to pivot your dataset in the ElastiCube-model to make it usable in your scatterplot.

You could do something like this to pivot your dataset:

select a.[Code]
, a.[Part]
, a.[ICD-10]
, a.[Sex]
,a.[Year]
,a.[Rought Rate]
, '0-4歳' as Age_group
, a.[0-4]
from [Book1.csv] a
union all
select a.[Code]
, a.[Part]
, a.[ICD-10]
, a.[Sex]
,a.[Year]
,a.[Rought Rate]
, '5-9歳' as Age_group
, a.[5-9]
from [Book1.csv] a

HamzaJ_0-1637142174139.png

Extend the query to incorporate all your age groups. This should make it workable for your scatterplot.

Hamza

szimmermann
8 - Cloud Apps
8 - Cloud Apps

Are you using the Scatter Chart? I assume you want to represent the sums of each age group, grouped by part and then by year. If so, try adding the date to the X axis, the sum of the age groups to the Size field, and the part to the Break By field. You could also add part to the Y axis ... it depends how you want to visualize it. I hope this is helpful!

One thing I've picked up along the way I thought I would share is that it is often beneficial to start your widget as a pivot, get the data to look how you want it to look, and then switch the widget type to the more visual type. This doesn't always work, and didn't work for Scatter Chart, but it has helped me a lot in other scenarios. 

Let us know how it goes!