I’m often asked ‘How do I/Can I create my own calculations in DV?’, so I thought that I’d put up a short blog on how to achieve this. I’m going to quickly create some sample data that show some simple sales figures for 99 offices. I’m then going to create 10 buckets ( or ‘bins’ ) and get DV to show who’s generating (or not) all the sales and allow me to focus on them.
Here I have a table with random distribution of sales figures across offices
If I log into DV and use that data I see this
Create a project and click on the ‘Add Calculation’
This pops up an editor to allow us to define a new calculated item.
Now select Bin from the Aggregates. You can see from the description that there’s a lot of detail of what it does, how it works, etc.
This provides a template for the command, so let’s create one step by step. As we are looking to create bins of offices by their sales figures, let us start by dragging ‘SALES’ into the ‘measure’ as shown.
Now let us dimension that by office. DV is knows which are measures and which are dimensions, so just click on ‘dimension’ in the calculation field and it will pop up the list of possible dimensions ( in our case this is just OFFICE ).
Click on it and you should now see the formulae taking shape
We don’t want an additional ‘Where’ command and we do want 10 buckets, so edit as shown. Call it ‘SalesBins’ and press validate to validate it.
Now save the new calculated item. We can now freely use it in our analysis to perform bucketing.
A pivot quickly gives us the following, showing sales by office neatly arranged into the 10 buckets.
If we apply a Scatter Cat visualisation and colour it by bucket we can get a great view of everyone.
So we have shown how to create and use a sample calculated item. We chose ‘bins’ as it’s used quite a lot, but of course we could apply a clustering to achieve something ‘similar but different’. Here I have applied a K-Means cluster and have clustered the offices. I have retained the colouring of the bins so that you can see how this apportionment has taken place. That’s a topic for a future blog post.