6 Replies Latest reply on Sep 28, 2017 10:49 AM by satish.parvathaneni

    Don't know how to create a simple cumulative count chart where BINS are created by R script.

    Peter Sabela

      Can you please help me with this:    I have minutes value in each record,  I create a custom bins for minutes using R.  I am not able to create a simple cumulative count chart (where bins are on x axis).

       

      Data:

      Calculated field:  Custom_bin for minutes using R:

       

      SCRIPT_INT("

      bins <- c(0,30,60,120,240,1440,2880,7200,300000)

      findInterval(.arg1,bins)

      ",sum([Minutes]))

       

      Current Viz (count of items in each bin, but need cumulative as of each bin):

       

       

       

       

       

      Tableau package version 10.2

        • 1. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.

          Hiya Peter Sabela,

           

          I've ping-ed Jonathan Drummey about this as I know he's quite well versed into R.

          Thanks for your time

           

          ----------

          Lénaïc RIÉDINGER, Global Community Engineer Tableau

          Tableau Community Forums | Knowledge Base

          If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!

          • 2. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.
            Jonathan Drummey

            Thanks for the shout-out, Lénaïc!

             

            R is not necessary for this and in fact introduces a whole level of complexity that is not necessary to get the desired results given this view. Here's a view that I built using calculations to get the totals on the bins, note the extra table calc pills on Rows and Filters:

             

            Screen Shot 2017-08-04 at 1.32.40 PM.png

             

            Now here's a view where I used a record level calculation (instead of the R calculation) to create the bins. This record level calculation can be used as a bin and then the records can be summed. There are only two pills in the view, the bin calc and SUM(Number of Records):

             

            Screen Shot 2017-08-04 at 1.33.38 PM.png

             

            One of the key concepts I'm using here is that there are 4 levels of computation in Tableau, in order they are:

             

            - record-level. These are fields from the data source and record-level calculations. They have the maximum flexibility in their use, we can use them as dimensions or measures. These are computed in the data source.

             

            - regular aggregate. These are SUM(), MIN(), MAX(), etc. and are always measures. These are almost always computed in the data source, data blends are the exception.

             

            - table calculations, including R and Python scripts using the SCRIPT_ functions. These are always measures. We can nest table calculations inside other table calculations but that can get quite complicated, especially when we want to use table calculation results more like a dimension as in this case. These are almost entirely computed in Tableau, the exceptions are some instances of the TOTAL() table calculation plus of course R and Python scripts.

             

            - post-aggregate computations like reference lines, grand totals and subtotals, trend lines, clustering, and forecasting. These are performed after all other calculations are complete and are almost entirely computed in Tableau, the exceptions are some reference lines using the Total aggregate and some grand totals & subtotals.

             

            So in looking at this workbook I could see that the R script was returning a bin and you were trying to use it as a dimension. Since table calculations & R scripts can't be used as dimensions therefore my question was "Is what the R script is doing something that we can compute in Tableau as a record level value that can be used as a dimension?" and the answer so far as I could tell was yes so I built the Bin jtd calculation.

             

            v10.2 workbook is attached, let me know if you have any questions!

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.
              Peter Sabela

              Hi Jonathan,  Thank you very much for your time and explanation.   Your answer is very helpful.  In general,  I have been trying to learn when is a good case to use R in Tableau.   In the example above, I was not certain why I could not make a progress.  Now I know.   Thank you again.

              • 4. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.
                Jonathan Drummey

                You're welcome!

                 

                When it comes to Tableau's R integration (or Python starting in v10.1 or Matlab in v10.4) what I think about is the following:

                 

                1) When there is a calculation that requires iterating over values multiple times to generate a result, for example machine learning, some more advanced statistical algorithms, doing nearest neighbor analyses, creating force-directed plots for network analysis, etc. then we need to use one of those languages because Tableau doesn't really have support for that kind of iteration.

                 

                2) While in Tableau we have calculated fields, sets, bins, clustering, trend lines, etc. and more available (and they can do a lot) if someone is more experienced with R/Python/Matlab then they might resort to those rather than trying to learn all of Tableau's intricacies.

                 

                3) That said the dimension vs. measure distinction is fundamental to Tableau and if we want to use a field as a dimension we can't get that with these integrations because the results are table calculations that are always measures. In that case if Tableau's calculations and other features can't support the necessary computation and we really do need R/Python/Matlab to do the computation then I'd do that prior to Tableau. Or (worst case) for some desired workflows Tableau just isn't suitable for the task, for example Tableau's R/Python/Matlab integration doesn't really support write-back so the kind of iterative model-building that some data scientists want to have doesn't flow well in Tableau.

                 

                Jonathan

                • 5. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.
                  satish.parvathaneni

                  Hi Peter ,

                   

                  Is there a way a R script can be scheduled using windows command ?

                   

                  Just looking a create a bat file and looking to schedule it on a daily basics to generate a .xls .

                   

                  Any help is greatly appreciated . Thank you !!

                   

                  Regards,

                  Satish

                   

                   

                  • 6. Re: Don't know how to create a simple cumulative count chart where BINS are created by R script.
                    satish.parvathaneni

                    Hi Jonathan,

                     

                    Is there a way a R script can be scheduled using windows command ?

                     

                    Just looking a create a bat file and looking to schedule it on a daily basics to generate a .xls .

                     

                    Any help is greatly appreciated . Thank you !!

                     

                    Regards,

                    Satish