-
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))}
-
2. Re: How to calculate sum of square of each element in the group
Jim DehnerApr 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
-
-
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?
-
4. Re: How to calculate sum of square of each element in the group
Jim DehnerApr 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.
-
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:
-
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
-
7. Re: How to calculate sum of square of each element in the group
Jim DehnerApr 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
-
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
-
-
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
-
-
11. Re: How to calculate sum of square of each element in the group
Deepak RaiApr 9, 2018 3:23 PM (in response to Uday Kintali)
-
Each Value Square.twbx 12.6 KB
-
-
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
-
-
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?
-
14. Re: How to calculate sum of square of each element in the group
Deepak RaiApr 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