In this post I will explain why the excel file I’m using only has 4 months worth of transfers. The answer is simple: when a search is performed on https://my.chep.com/ website the transaction report gets truncated if there are to many results.
Practically all the excel files I have are truncated to ∼500 rows.
Another issue with my excel file is that the search was done using default filters, this means that the data was filtered by “Create Date” value, which is not good for my chart.
As date in “Create Date” column can be sometimes months apart from shipment date, I can’t use it as a filter on CHEP portal as it will not produce accurate results for my chart: it might add deliveries I do not need or exclude deliveries that I require.
In the image above, deliveries for February were selected, but since they are filtered by “Create Date” value, “Shipment Date” has values for previous years August and September, which I do not want.
Luckily there is an option to filter data by “Shipment Date” value. I can’t use “Delivery Date” filter as it sometimes contains no values.
In order to get around the limitation of ∼500 rows per excel file, I initially thought that I will need to download excel file for every month of transfers and combine it to one file manually, however there is a way to add more then 500 rows to the original excel file, that gets generated on https://my.chep.com/ website.
Every time search results for transaction report on https://my.chep.com/ website return more then 500 rows, a “View More” button appears. Every time it is clicked it will add additional ~500 rows to search results. You can click it several times, until all rows are added to results.
Once there are now more rows left to add, the “View More” button disappears. I started this project with an excel file that contained 509 rows, however the actual results for half a year of transfers is 2164. And since these 2164 rows were collected using “Shipment Date” as a filter, the data I know hold in an excel file is accurate. And once I uploaded this new excel file to database, I can see the change in results compared to previous results.
This is the same supplier I used as an example in earlier posts, where it appeared that for some months it delivered almost 300 CHEP pallets, and for some none at all, however after updating excel file I can see that my first assumption was wrong, as it was based on incomplete data.
I should have done this research at the very beginning of my project, but instead I started working with an incomplete data. The bright side of this discovery is that now my idea to use month by month charts looks a lot better, as it appears all suppliers are delivering CHEP pallets regularly and there a very few who use CHEP pallets occasionally. This research also gave me a new idea for one more chart.
Idea for new chart
When researching, I found that there are 8 types of transfers in total and I intend to use this knowledge to draw one more chart on my website.
Basically I want to have a bar chart for each transfer for every month, similar to how I intend to draw a chart for each supplier. An example of this chart for one moth is below:
We mainly use two transaction types: “Returns” and “Transfer In”. Every time a delivery arrives on CHEP pallets, a supplier makes a transfer on https://my.chep.com/ website to transfer pallets to us. This transfer to us is of “Transfer In” type. Once we have enough pallets on our site, a collection is organized and we return those pallets. This transfer type is “Returns” on CHEP portal. I think a chart were you can see monthly how many pallets came in and how many pallets left our site is really helpful. That’s it for today 😉