Please see that attached dashboard. My question is very similar to the question asked here. I know I only used Tableau for 1.5 years, but more and more I find myself needing to combine Tableau with either R or Python. If you know someone who is an expert in that integration or even an expert in Window functions, could you please reach out to them and show them this question.
In this now I am trying to do an average, and trying to help one of my good friend in Tableau. Can anyone help? I would like to walk you through the steps I have taken so far over the last month so you understand the problem better.
Attempt 0) For some reason, I cannot get the Windows function to calculate the window average correctly. How does one properly do window sums of window sums? In fact, related to this my friend found that the Grand Total function will not take in filters or levels (drill down/up). I am partially wondering if Window function has issues taking in filters, levels, etc. as well, but I could not figure it out.
So, I tried to do it in R by using mean(), lapply() and sum(), and even just the sum() function in Attempt 1, McalcProblem, and Attempt 2 worksheets.
Attempt 1) The first time I got an output, put it was still outputing multiple sum instead of the grand total. Because it then outputed a vector instead a singular value this brought me the question, how can I better debug and monitor for a particular worksheet what is being sent from Tableau to R? Since, I could not figure out the answer, I copy and pasted the data and then tested it directly in R. Eventually what I learned, I used to make Mcalc as seen in the Mcalc worksheet.
Attempt 1) Mcalc was the Rcode to do everything even the average. Unfortunately, when I put it in Tableau it did not integrate properly? So I tried the paste() function and filtered over something besides profit. I got a reasonable answer, but still Tableau would not take a singular output of a sum. Unfortunately as I mentioned before, if I remove paste() function and make the script real the output will be blank from this worksheet. I am not sure what " " actually means. Is the output in from R affect by Nulls or get error values due to them? Can anyone help debug this sheet to get a singular value?
Attempt 2) So I have tried using both R and Window Sum's together. In this attempt I finally got things to calculate correctly. \o/ \o/ . The only problem is if I change the filter from non-null of "Sales$Previous...." to "Profit$Previous...." things break down an I am in the problem that I started with. How does one properly set up indexes in Tableau for Table Calcs especially when the index is (self-referential) over the same measure/dimension as what your trying to calculate?
1) How does one properly do window sum's of window sums? In more detail for example, how does one properly set up indexes in Tableau for Table Calcs especially when the index is (self-referential) over the same measure/dimension as what your trying to calculate.
2) How can I better debug and monitor for a particular worksheet what is being sent from Tableau to R? I have read its unaggregated data and a bit about how Rserve() works, but I cannot seem to find in R that variables from Tableau loaded into R.
3) What does the " " output means in Tableau coming from R? Is it due to nulls? Can only help me particular debug this sheet in Tableau?
R temp example2.twbx 519.9 KB