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

[Mis]

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

[HamzaJ]

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

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

Hamza

[szimmermann · Accepted solution]

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!