Utilize Pivot App for cashflow from multiple milestones
I have a sheet that tracks milestone payments associated with a specific type of project contract. Each row is a contract and columns are payment dates or amounts (see screenshot below). There are up to 5 milestone payments for each contract resulting in 5 pairs of columns (date and amount).
I was hoping to use Pivot App to create a cash flow report that shows contract payments throughout the year. Each row would be a contract and each pivot column would be a month. The values would be the dollar amounts for that month.
Pivot App only allows you to select one column in your source data to generate the pivot columns. Because I have 5 milestone payments, I have 5 columns of dates.
Eventually, there will be other sheets of other contracts on the same projects that I'd like to be able to bring in as well, resulting in hierarchical rows in the pivot table (project = parent, contract = child).
Can anyone think of a creative way to get the 5 date and 5 amount columns into 1 date column and 1 amount column so that I can pivot off of it? I have access to other apps like DataMesh and DataShuttle, if helpful.
Answers
-
Not sure if this will help you, but I have a similar Smartsheet tool related to Billing Milestones that has a row for every billing milestone (each payment milestone is its own row). While this may seem weird coming from a project-based set of rows, it is actually simplifying in a lot of ways. We have a drop-down column called "Payment Trigger" that includes values like "1 of 3 (33%)", "2 of 2 (50%)", "monthly", "annual", "complete", "1 of 1 (100%)", etc. We also have columns for Invoice #, Invoice Date, Date Rec'd, Date Deposited, etc., and this makes everything more manageable through Reports, DVs, etc. I generally take this sheet and export to Excel whenever I need to actually have a pivot table, but I think this would allow you to do the single date pivot using Pivot App if you needed to.
Let me know if useful to show-and-share in a live meeting. Would be happy to help you brainstorm a solution if you haven't found one yet!
Best,
Will
