Since I had some spare time, I decided to play around with excel pivot tables and charts and see if I can achieve similar results to what CHEP PALLETS REPORT (flickerbook.co.uk) does. I do like results of it, however this approach is a bit more time consuming compared to CHEP PALLETS REPORT (flickerbook.co.uk), as it requires some manual labour to produce the results as opposed to it being automated. But I’ll explain what I have done. I also want to have content for my excel section of this page, so this exercise proved beneficial to me.
Creating column chart for monthly pallet transfers by suppliers V.1.
So for this exercise I used excel file that contains cleaned data for half a year CHEP pallets transfers which can be found here.
I first created a pivot table in excel and arranged data in this order:
Since I want to order data by date and only show transactions that are of “Transfer IN” type, I added filters for shipment date and transaction type. However, I don’t have an option to separate date by months. What I should have done is create a new column, where I copy shipments dates and convert the date format to mm-yyyy. I will do that on the version 2 of this exercise. For this one I did it a bit differently, although this method is a lot more time consuming. Since I have data of half a year transfers, I created six identical pivot tables for each month. Then in filters sections I would select “Transfer In” for all six of them, and for shipment date I selected different month transfers to create six pivot tables that store monthly transfers for each supplier.
Below is an example of 3 pivot tables that contain 3 months of transfer data by each supplier.
Once I created these six pivot tables and filtered them to display monthly data, I copied data for each month into a new excel spreadsheet. The column next to the copied data I used to fill with year and month value: after pasting every months data from pivot table I would add yyyy-mm date value manually.
After all the data was copied, pasted and year-month values added I sorted the columns by suppliers names.
I then produced a chart based on this data, but it is not as helpful as it could be, as it just displays bar chart next to suppliers name.
In order to render a better chart I did some more manual work on these 3 columns: I separated each suppliers data by empty row, add 0 pallet quantities if the supplier didn’t deliver any pallet for that month (in the picture below these are marked in orange), in the first column deleted repetitive suppliers names.
When I render a chart in excel using this newly arranged data, I get a chart that is very similar to CHEP PALLETS REPORT (flickerbook.co.uk) monthly pallets by supplier chart.
However if I would have to produce a chart the way I have done in this post so far, I would not be happy. This method requires me to do a lot of things manually (mark dates in pivot tables filter, copy and paste data, add yyy-mm values manually next to copied data, insert 0 pallet quantities when a supplier did not transfer any pallets) which leaves a lot of room for error. The main reason I still did this, was that I wanted to practise using excel pivot tables, and in this exercise I produced 6 of them. Repetition is the mother of learning!
The finished excel file for this chart can be found here.
Creating column chart for monthly pallet transfers by suppliers V.2.
As mentioned earlier, to make my life easier I should have created a new column in the transaction report that contains year and month value. In this version this is exactly what I did. I created I new column named “Year Month”, copied data from “Shipment Date” column, pasted it into the new column and changed it’s date format to “mmm-yy”.
Once I created a pivot table for this transaction report with additional column, I filled drag field as demonstrated below:
The “Months” and “Year Month” rows appear once I drag the “Year Month” column to rows field. Once the “transaction Type” filter is set to “Transfers IN” this pivot table gets created:
In this pivot table I already have data grouped for each supplier by month, which is what I wanted. If I render a chart using data from this pivot table I get this result:
I’m happy with the result. Adding additional column that helps me group transfers by month made the process of rendering a chart a lot easier. It’s funny I didn’t come up with this at the start of this exercise, because when developing CHEP pallets report database table, I added a column to store year and moth value that enabled me to group data by month in SQL queries. However I still prefer using CHEP PALLETS REPORT (flickerbook.co.uk) over excel pivot tables and charts, as it highlights different suppliers a lot better by using colours. But this chart has an advantage of it’s own, by just one click on a month name in pivot table I can see transfers done by date in the chart.
Excel file for this chart can be found here.
Conclusion
Excel pivot tables and charts can be used to search for unusual CHEP pallets transfers. Using pivot tables I was able to produce similar chart to CHEP PALLETS REPORT (flickerbook.co.uk) monthly pallet transfers by suppliers chart. However the benefits of using the CHEP PALLETS REPORT (flickerbook.co.uk) are these:
- Automation: all that needs to be done is the upload of the transaction report file.
- If supplier hasn’t delivered pallet for a specific month, it is represented in a chart.
- Chart titles display time period for which the chart is generated (it can be used to check if a full months data is being used).
- Colours are used to distinguish suppliers in the chart.
For me the major benefit of this exercise was working with excel pivot tables and excel charts, as I feel I need more practise with excel and must come up with more ideas on how to do it. Especially it was helpful that I was working with data I used previously, since I knew what results to expect from a chart.