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.
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.
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.
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.
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
On 23 March 2016 at 07:10, Dumisani Nyumbeka <firstname.lastname@example.org>