We can do it in tableau.
first calculate the total full time and total part time
[Total full time]
sum([Male Full time])+sum([Female Full time])
Total part time
sum([Male Part time])+sum([Female Parttime])
[Total full time]/[Total part time]
Fisrt I want to confirm one thing, How data is populating in your Database, If your data is not SENSITIVE, please share sample data then can easily create the calculatuion & supply the entire solution.
since getting confusion whether Header1 is Field name or data of (Record) of Field.?
If Your data populate like below, then follow the approach.
Emp_Name Part/Full Hours
A Female_Parttime 50
B Female_Fulltime 100
C Male_Parttime 100
D Male_Fulltime 300
1) Create a calculated Fields as below,
Name: Full Time Hours
sum(if contains([Part/Full], "Full") then [Hours] end)
2) Create another calculated Fields as below,
Name: Part Time Hours
sum(if contains([Part/Full], "Part") then [Hours] end)
3) Create another calculated Fields as below,
Name: Full/Part Ratio
Syntax: [Full Time Hours]/ [Part Time Hours]
Last calculated field gives you expected solution.
Thank You Sankar Rajan. I appreciate the help yet I forgot to mention how its setup.
Hello Tableau Kumar,
My data is setup as follows:
Male has FullTime and Halfime . For example
PartTime F 1
PartTime M 2
PartTime M 3
PartTime M 5
PartTime F 5
PartTime F 12
PartTime M 19
PartTime F 27
FullTime M 59
FullTime M 81
FullTime M 85
FullTime F 96
FullTime F 118
FullTime M 128
FullTime F 153
FullTime F 210
Sorry did get not your data, i am doing my best to understand your data.
I thought your data located in two Different Fields namely Male, Female.
EMP Male Female
E1 (M) FullTime M 128
E2 (F) FullTime F 118
E3 (M) FullTime M 85
E4 (F) FullTime F 153
E5(M) PartTime M 130
E6(F) PartTime F 110
E7(M) PartTime M 85
E8(F) PartTime F 150
I thought we have to extract Hoours information from your data, if it is right, then follow the below lengthy process & It is absolutely accurate.
Create following calculations.
1) Name: Male Fulltime HRS
Syntax: sum( int( if contains([Male], "FullTime" ) then mid([Male], findnth([Male], " ", 2)+1) end)
2) Name: Male Parttime HRS
Syntax: sum( int( if contains([Male], "PartTime" ) then mid([Male], findnth([Male], " ", 2)+1) end)
O/P : 215
3) Name: Female Fulltime HRS
Syntax: sum( int( if contains([Female], "FullTime" ) then mid([Male], findnth([Male], " ", 2)+1) end)
4) Name: Female Parttime HRS
Syntax: sum( int( if contains([Female], "PartTime" ) then mid([Male], findnth([Male], " ", 2)+1) end)
5) Name: Total Full Time HRS
Syntax: [Male Fulltime HRS] + [Female Fulltime HRS]
6) Name: Total Part Time HRS
Syntax: [Male Parttime HRS] + [Female Parttime HRS]
Syntax: [Total Full Time HRS] / [Total Part Time HRS]
O/P: 384/375 = 10.24
I hope these calculations are helping you.
Sankar and Kumar have tried their best but I recommend you to provide the data, since every perspective of problem one can't explain. There is a possibility that this calculations won't work even if there are right reason being improper data structure.
Please provide the sample for the same.
its just a matter of seconds.
Thanks and Regards,
Thank You Ashish,
Since it's sensitive data I cannot provide the data but will try to make a similar one,
The file is extracted data from an excel file. The category is:
FullTime/PartTime , Gender, ID .
The FullTimePartTime have an option of seeing FT or PT or both.
As for Gender it has an option of seeing M or F or both.
ID its 1 2 ...
sample Data.xlsx 31.5 KB