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

Uday Kintali Apr 9, 2018 9:23 AM

Hi ,

I have a table like this :

Category	Subcategory	type	Value	SumofSquares ( Calculated Field)
a	a1	no-change	10
	a2	test	15	sqrt(10^2 + 15 ^2 + 30 ^2)
	a3	test	30	sqrt(10^2 + 15 ^2 + 30 ^2)
b	b1	no-change	5
	b2	test	10	sqrt(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.

Could you please help me understand how to calculate this ?

Please let me know if you need more information.

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

Eric Hammond Apr 9, 2018 9:35 AM (in response to Uday Kintali)

{Fixed [Category]: SQRT(SUM([Value]^2))}
Like Show 0 Likes(0)

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

Jim Dehner Apr 9, 2018 9:37 AM (in response to Uday Kintali)
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.

sqrt problem jd.twbx 15.3 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: How to calculate sum of square of each element in the group

Uday Kintali Apr 9, 2018 11:02 AM (in response to Uday Kintali)

Thanks Jim and Eric for your reply.
@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?
Like Show 0 Likes(0)

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

Jim Dehner Apr 9, 2018 11:33 AM (in response to Uday Kintali)

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.
Like Show 0 Likes(0)

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

Eric Hammond Apr 9, 2018 1:20 PM (in response to Uday Kintali)

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:
Like Show 0 Likes(0)

Actions
6. Re: How to calculate sum of square of each element in the group

Uday Kintali Apr 9, 2018 1:47 PM (in response to Uday Kintali)
Thanks for your reply, Jim.

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
@Jim --I have shared the sample workbook, could you please look and suggest? Please let me know if you need more info.

Tableau version = 10.3

Sample_Framework.twb 40.1 KB
Like Show 0 Likes(0)

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

Jim Dehner Apr 9, 2018 1:59 PM (in response to Uday Kintali)

could not open the workbook
you need to extract the data and post a twbx workbook
thanks
Jim
Like Show 0 Likes(0)

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

Uday Kintali Apr 9, 2018 2:42 PM (in response to Jim Dehner)
Attached is tbwx file. Does this work ?

Sample_Framework.twbx 15.4 KB
Like Show 0 Likes(0)

Actions
9. Re: How to calculate sum of square of each element in the group

Jim Dehner Apr 9, 2018 2:59 PM (in response to Uday Kintali)

this is what I get

did you export a packaged workbook?

Jim
Like Show 0 Likes(0)

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

Uday Kintali Apr 9, 2018 3:05 PM (in response to Jim Dehner)
Thanks for that screenshot , Jim.

I exported the data now and attaching updated workbook.
Hope you are able to see it now.

Sample_Framework.twbx 15.4 KB
Like Show 0 Likes(0)

Actions
11. Re: How to calculate sum of square of each element in the group

Deepak Rai Apr 9, 2018 3:23 PM (in response to Uday Kintali)
Hey Uday,
You need to use this:
Thanks
Deepak

Each Value Square.twbx 12.6 KB
Like Show 0 Likes(0)

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

Uday Kintali Apr 9, 2018 4:18 PM (in response to Deepak Rai)
Deepak,
Thanks for you reply.
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.

Sample_Framework.twbx 15.4 KB
Like Show 0 Likes(0)

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

Uday Kintali Apr 10, 2018 9:37 AM (in response to Uday Kintali)

Jim Dehner / Deepak Rai -- were you able to open my file?
Like Show 0 Likes(0)

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

Deepak Rai Apr 10, 2018 9:49 AM (in response to Uday Kintali)

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
Like Show 0 Likes(0)

Actions