1 2 Previous Next 21 Replies Latest reply on Apr 11, 2018 2:29 PM by Jim Dehner

# How to calculate sum of square of each element in the group

Hi ,

I have a table like this :

CategorySubcategory type
Value SumofSquares ( Calculated Field)
a

a1

no-change10
a2test15sqrt(10^2 + 15 ^2 + 30 ^2)
a3test30sqrt(10^2 + 15 ^2 + 30 ^2)
bb1no-change5
b2test10sqrt(5^2 + 10 ^2 )

I am trying to create the last column based on the other columns in the above table. So, irrespective of how many sub-categories I have for each group, I want to create  a calculated field which calculates sums of squares of each value.

• ###### 1. Re: How to calculate sum of square of each element in the group

{Fixed [Category]: SQRT(SUM([Value]^2))}

• ###### 2. Re: How to calculate sum of square of each element in the group

Hi

see the attached

I broke it into pieces

first the square of the value then the sqrt of the sum of the squares 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
• ###### 3. Re: How to calculate sum of square of each element in the group

@Eric Hammond  - that solution did not work.

@Jim - solution you gave worked for one set ( where there were 3 rows in the bucket) , but did not work for another set of data ( with 2 rows). Your solution seems to be generic but not sure why it didn't work.

One detail probably I missed in the description was that the calculated field I want to calculate is based on another calculated field. formula for calcualted field 1 = =G3/F3

calcualted field 2 = =SQRT(H3^2+H4^2+H5^2)

Do you think approach you suggested will change with above data?

• ###### 4. Re: How to calculate sum of square of each element in the group

in the file I sen I excluded the 'No-change" row from the print out - is that what you want?

other than that the formula does not know the difference between 2,3,4,5 or n records - it will calculate the sqrt

I also see that you added some dimensions to the viz - the way Fixed calculations work is they take all the combinations of the dimensions that precede the colon and then aggregate them by what follows the colon - if the added dimensions Test Name and Label are important to partitioning the data for the SQRT calculations then they need to be included in the lod expressions --you would have to send me a file with the data and I will check it out

Tableau will not recognize the formula that you suggested at the bottom of the frame

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.

• ###### 5. Re: How to calculate sum of square of each element in the group

Hi Uday,

Jim Dehner's solution gives 11.18 for the set of data with 2 rows, which aligns with how I understand the original question.  Jim's solution will also handle the new calculation mentioned in your last comment - just modify the calculated fields.  The same result can be achieved by building on the calculated field that I suggested earlier - here is how that version looks: • ###### 6. Re: How to calculate sum of square of each element in the group

To clarify above points and to provide more info about my question. I put some dummy data in a tableau and created the view I need.

questions I have :

• I applied the logic Jim shared and I am getting incorrect value for one and correct value for one type of test_name. More points below

@ Eric Hammond / Jim :

For Test_name = T1 : calculation of stddev_puchasers_lift is correct

For Test_name = T2 : calculation of stddev_puchasers_lift is not correct, when calculating manually i am getting this value as 0.0425

• in the tableau, lower_95_lift = I want to calculate as purchasers_lift +/- 1.96 * stddev_purchasers_lift -- but Tableau gives an error saying cannot mix aggregate and non-aggregate fields. Any thoughts on how this shoudl be done?
• Purchasers_signicance : This field is a filter which I want to apply only to treamtment rows, but if I try to add a filter if variation_type = 'treatment' I ge tthe same error as above - cannot mix aggregate and non-aggregate fields

Tableau version = 10.3

• ###### 7. Re: How to calculate sum of square of each element in the group

could not open the workbook

you need to extract the data and post a twbx workbook

thanks

Jim

• ###### 8. Re: How to calculate sum of square of each element in the group

Attached is tbwx file. Does this work ?

• ###### 10. Re: How to calculate sum of square of each element in the group

Thanks for that screenshot , Jim.

I exported the data now and attaching updated workbook.

Hope you are able to see it now.

• ###### 12. Re: How to calculate sum of square of each element in the group

Deepak,

I think the earlier dataset I provided doesn't scale to the revised dataset I shared. I had shared another tableau file.

Attaching it again. I want the solution on this one , if you scroll up a bit you will find all the details.

Please let me know if I wasn't clear.

• ###### 13. Re: How to calculate sum of square of each element in the group

Jim Dehner  /  Deepak Rai -- were you able to open my file?

• ###### 14. Re: How to calculate sum of square of each element in the group

Hi Uday,

I could not open, but it seems that it is something different from your original question. I would rather suggest you to close the present thread and start the new one, since your original question was answered.

Thanks

Deepak

1 2 Previous Next