6 Replies Latest reply on Oct 18, 2018 11:13 AM by Thomas Eberline

# Creating a calculation from specific row values

I am trying to build a "score" by calculating specific datapoint values from my dataset that is uploaded below. I have dimensions: [movement_id] [_0_datapoint_key] and measure: [_0_datapoint_value]. I am looking to build a calculation that combines multiple [_0_datapoint_value]s together, but am struggling to accomplish this goal. I have attempted fixed LOD calculations, sets, groups, bins etc and am not having any success. I was expecting a returned value between (40 - 100), and am getting (14,000 - 55,000)The formula that I am trying to use is:

({ FIXED [_0 Motion Id]=6: (SUM([_0 Datapoint Value]*0.5))} + { FIXED [_0 Motion Id]=7: (SUM([_0 Datapoint Value]*(0.5/2)))} + { FIXED [_0 Motion Id]=19: (SUM([_0 Datapoint Value]*(0.5/2)))}) * [Weight]

this tells me that it is valid, but I then have to combine this with filters in order to get only the specific [_0_datapoint_value]s that I am looking to use.

I have not been using Tableau for long (caveat in case this is a stupid question), so any help would be greatly appreciated. Thanks!

Message was edited by: Thomas Eberline

I made another failed attempt at this. I made the following calculated fields:

Calculated Field Name: motion_id_6

(IF [_0 Motion Id] = 6 AND [_0 Datapoint Key] = 'squat_depth_pct_lt' THEN { FIXED [_0 Datapoint Value]: SUM([_0 Datapoint Value])} END)

Calculated Field Name: motion_id_7 (IF [_0 Motion Id] = 7 AND [_0 Datapoint Key] = 'jump_height_pct' THEN { FIXED [_0 Datapoint Value]: SUM([_0 Datapoint Value])} END)

Calculated Field Name: motion_id_19 (IF [_0 Motion Id] = 19 AND [_0 Datapoint Key] = 'jump_height_pct' THEN { FIXED [_0 Datapoint Value]: SUM([_0 Datapoint Value])} END)

Calculated Field Name: combined ([Squat]*0.5) + (([Vertical Jump]*(0.5/2)) + ([Depth Jump] * (0.5/2))) * [Weight]

this says it is valid, but I cannot get it to populate in the viz field. Any ideas would be greatly appreciated.

• ###### 1. Re: Creating a calculation from specific row values

Hi Thomas

see below

you have nulls in the data

Tableau will return a Null when it tries to aggregate date that contains nulls

to get around that wrap the measure in zn() like

Calculated Field Name: motion_id_19 (IF [_0 Motion Id] = 19 AND [_0 Datapoint Key] = 'jump_height_pct' THEN { FIXED [_0 Datapoint Value]: SUM(ZN( [_0 Datapoint Value]))} END)

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Creating a calculation from specific row values

I posted a link to a workbook that I put together with all of the calculations and datasource. I thought it might be helpful, as I cannot get the calculated field to show up in my viz window. I am trying to show the combined_score value for each associated session guid.

Thank you for your help and suggestion, but I am still not having success.

• ###### 3. Re: Creating a calculation from specific row values

Hi you need to aggregate the values in the combine formula and include the zn on weights

I don't know th analysis you are doing so I selected avg(weight) but it could be min or max depending on what you are doing

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Creating a calculation from specific row values

Thank you! That is exactly what I was trying to accomplish. I really appreciate it!

• ###### 5. Re: Creating a calculation from specific row values

thanks

Jim