
1. Re: Grouping Formula
Simon Runc Oct 10, 2018 5:52 AM (in response to Gary Cantigneaux)hi Garry,
So with regards your first part, you can do this as a "row level" calculation in Tableau. This works the same as if you'd done it in the data
[Column1] + [Column 2]  [Column 3]
To get an aggregated result, at what ever level is in you viz, you'd change it to
SUM([Column1]) + SUM([Column 2])  SUM([Column 3])
so the values are SUMed before being combined.
One note that if any of your columns are blank it will return null (1+1+null = null), so you might want to wrap a ZN around the fields
zn([Column1])+ zn([Column 2])  zn([Column 3])
This just means nulls will be treated at zeros. Worth noting when using calculating involving multiple fields (unless you'd want a null, of course!)


3. Re: Grouping Formula
Gary Cantigneaux Oct 11, 2018 5:45 AM (in response to Simon Runc)Hi Simon,
Thanks a lot for your feedback and help, I did it in a tableau file, so it is easier. When you say we can do it in a “row level” calculation, you mean a calculated field in the view?
I added the following formula: zn()+ zn()  zn() as proposed by you
Now this is the starting table:
And this is the final result:
So it looks like it is working for the moment.
I will try to get the real formula I need to use and get back to you.
Thanks a lot
Gary

image002.png 36.6 KB

image001.png 46.4 KB


4. Re: Grouping Formula
Simon Runc Oct 11, 2018 6:10 AM (in response to Gary Cantigneaux)hi Gary,
Glad it helped. So by Row Level and Aggregate Calculation I mean that for some calculation types (row level) the calculation is performed rowbyrow in the data, so the result of that calculation is irrelevant of what is in the VizLoD (the dimensions on the canvas), but for aggregate calculations the result is dependent on what is on the canvas. I refer to these as Off Canvas and On Canvas calculations. I wrote a Quora answer on this, that you might find useful (basically, IMHO, if you get this concept, you get Tableau!)
hopefully the quora post helps see how
[Column1] + [Column 2]  [Column 3]
and
SUM([Column1]) + SUM([Column 2])  SUM([Column 3])
are different.
In the first one the [Column1] + [Column 2]  [Column 3] is done on every row and then you bring in the result as a SUM (say) at whatever level you have on the canvas
on the SUM([Column1]) + SUM([Column 2])  SUM([Column 3]) the SUM is done at whatever level is on the canvas, and then the results of that are then used in the 1 + 2  3.
Hope that helps

5. Re: Grouping Formula
Gary Cantigneaux Oct 11, 2018 6:42 AM (in response to Simon Runc)Hi Simon,
I am sorry to say but it is not working, I have just tried with the following formula:
Stock Quantity / Forecast 1: as you can see it is ok when I am at the lowest level (figure 1) but when I aggregate by removing one of the row field (in this case IPC) then the FMC calculation 2 is not recalculation the formula but just summing the data of all the IPC in the column (Figure2)
Sorry but I am lost and I cannot find a solution.
Thanks for your help
Regards,
Gary
Figure1
Figure2:
I am also including the file in case this help you to help me ☺
Thanks,
Gary
PS: I am reading at the moment your article.
Regards,
Gary

Test input.xlsx 11.7 KB

Book1.twb 28.0 KB

image002.jpg 35.1 KB

image001.jpg 38.9 KB


6. Re: Grouping Formula
Simon Runc Oct 11, 2018 6:46 AM (in response to Gary Cantigneaux)I'm out for the rest of the day, but try using this version
zn(SUM([Column1])) + zn(SUM([Column 2]))  zn(SUM([Column 3]))
which should work at any level.

7. Re: Grouping Formula
Gary Cantigneaux Oct 11, 2018 7:15 AM (in response to Simon Runc)Dear Simon,
Thanks a lot, it looks like it is working. I hope now to be able to do this with the real data.
Thanks again for your support
Gary

8. Re: Grouping Formula
Simon Runc Oct 11, 2018 7:20 AM (in response to Gary Cantigneaux)Great news. If you read that Quora article is should make sense as to why one works and one doesn't