5 Replies Latest reply on Mar 23, 2016 1:24 AM by Carl Slifer

    How to generate weighted densities using Tableau and R

    Dumisani Nyumbeka

      I have a large dataset containing the user id and the user's portfolio balance within a specific period (the dataset is for multiple periods). so l want to to estimate the portfolio distribution across periods. Any ideas on how to do this..?

        • 1. Re: How to generate weighted densities using Tableau and R
          Carl Slifer

          Howdy Dumisani!

           

          Would it be fair to say your dataset has three columns?

          [ID],     [Time Period],     [Balance]

           

          Or is there more? If so what else is there? By portfolio distribution are you referring to various stocks that make up the portfolio over time or are you referring to a dsitrbitution based on the time balance. So you can see how often the user is at 10,000 or how often they are at 50,000 and if they stayed between 10-15000 mostly but had occasional spikes and valleys. That sort of thing?

           

          Send along a clearer idea here and I'll be happy to help. We may not even need to use R.

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: How to generate weighted densities using Tableau and R
            Dumisani Nyumbeka

            Hi Carl

            I have more columns but there are not necessary for these calculations.

             

            I want to get an idea of say within each time period how many unique clients had a balance of between 5000 and 6000,  6000-7000 and so on. However, in each time period l have about 6 million clients and my balances vary between -10000000 and +20000000.

             

            Portfolio balance just refers to a balance.

             

            Regards.

             

            Dumi

            • 3. Re: How to generate weighted densities using Tableau and R
              Carl Slifer

              Well Dumi,

               

              You are in luck! We are going to use a method called binning.

              1) Right-Click Balance and go to Create,

              2) then choose Bins.

              3) You can choose a bin size that is appropriate.

               

              You would then use this on the columns shelf and bring COUNTD(customer) to the text or to the rows shelf depending if you wanted a table or bar charts.  This will quickly show you how many people are in each separation.

               

              Now we may have issues because of the large size difference, so we might need to write our own custom binning equation. This would bin differently based on the range. For example when its (-100,000 , 100,000) we might bin by 5,000 blocks and when its above or below that maybe by 100,000 blocks. So let me know if we are after something like that instead. If not you may well need to split your data into big and small accounts just to visualize things easier.

               

              Cheers!

              • 4. Re: How to generate weighted densities using Tableau and R
                Dumisani Nyumbeka

                Hi Carl

                 

                Thanks for the solution but l still need to generate weighted densities and l guess that’s on me,  because l did not give all the information l and  apologise for that. Suppose l have another column  or  and l want to build weighted densities based on these dimensions.

                • 5. Re: How to generate weighted densities using Tableau and R
                  Carl Slifer

                  Hi,

                   

                  Mobile so excuse syntax pretty please.

                  If you're using another column and needing to weight by the density you are

                  really summing that value.

                  ID     Balance     Weight

                  1      1500          5

                  2      2000          1

                  3      2200           4

                   

                  If we binned on the balance and put that to the rows shelf and then took

                  the sum of weight to the text shelf of the marks card we get our weights.

                  If you want this as a percent of total, I'd then right click your

                  SUM(weight) on the text shelf and choose 'quick table calculation' then

                  choose '% of total'.

                   

                  The reason why the sum works and not fun multiplication is you need to

                  count each person individually and not sum up all the people and them sum

                  up all their weights and multiply. That would give a different type of

                  value

                   

                   

                  On 23 March 2016 at 07:10, Dumisani Nyumbeka <tableaucommunity@tableau.com>