Infrastructure Order Analysis
Previous4.3 Using KeeptheReceipts and Google Sheet for Procurement Data AnalysisNextMask Price Analysis
Last updated
Last updated
The downloaded file is a "xlsx" file. We can import it into Google Sheets. Open a new sheet on Google sheet. Under File on the top menu bar, select import, then select the upload tab and upload the file downloaded. Click select replace spreadsheet and then click on import data.
You may notice the naming of the columns downloaded from Keepthereceipt looks a bit different from the dashboard, but we could guess the meaning most of the time. If you have any questions on the meaning of these column names, you could refer to this page.
We are interested in which department spent most under infrastructure. We could use the pivot table feature in Google sheets to do this. The columns we are interested in then would be the "buyer_name", "order_amount_zar" and "payment_amount_zar".
To make a pivot table, we need to select the relevant columns on Google Sheet. Follow the below steps to make the pivot table
Click on insert in the menu and select pivot table
Select B to F column.
Click “create”
We can see a new sheet called pivot table is opened. On the right side is a list of columns. We can then drag the relevant columns under the rows, values, and columns menu under the pivot table editor. Because we want to see the sum of orders or payments by buyer name, we can
Drag the buyer name into rows.
Drag the order_amount_zar and payment_amount_zar under values.
Select sum under the summarize by dropbox to indicate we want to sum up all the order amounts by one buyer.
However, by this time, we found all cells in the sum of payment_amount_zar column has 0 as the sum. There is some problem happening here.
The issue is that Google didn’t understand the data under the "payment_amount_zar" column is numbers, so it’s treating these cells as plain text. It doesn’t make sense to sum up these text. Thus, we need to indicate to Google these values are numbers manually.
Let’s go to the previous sheet and inspect column "order_amount_zar" and "payment_amount_zar" . The "123" button on the menu bar indicates the data type. To change the data type of a column, we could follow below steps.
Select the column of interest and click on the 123 button. We can see the data type currently is automatic.
Change "automatic" to "number".
Switch to the pivot table sheet, we can the the sum of these amounts are showing in the pivot table now.
We would like to organize the pivot table to see the top spending department. Under buyer name in the pivot table editor, we could see Order by and sort by drop down box. This is used to sort the pivot table.
Under the "Sort by" dropbox, select the "sum of payment_amount_zar" option.
Under "Order by" dropbox, select "descending".
Now we could clearly see Gauteng Health and Gauteng Infrastructure development is the top spending department. Gauteng Health spent over 886 million Rand on infrastructure. While Gauteng Infrastructure development ordered over 2 billion Rand infrastructure, even though only 400 million Rand is paid.