Hello,
I have a pivot table that is populated from data in a table. The pivot also needs to include two additional rows of data that are a calculation based on data from previous rows in the pivot.
To render the two additional rows in the pivot a placeholder/dummy record was created and exists in the table, The data values in the source table for these placeholders are zeros and the expression needs to ignore the zeros from the table and perform the calcuation in the expression of the pivot.
Attached is a QVW that has the pivot in the desired format but needs the calculations added. A second pivot also exsits that shows what the results should look like along with a few notes to help explain how it is to function.
The QVW contains multiple snapshots of orders and multiple snapshots of Inventory. Each snapshot has an associated Version Date. The calcuations that need to be added are:
- Shortfall: Quantity from the most recent snapshot of Inventory minus the quantity from the most recent snapshot of orders.
- Cum Shortfall: Running total of the Shortfall calcuation (excluding month end quantities)
Any help, suggestions, recomendations on how to accomplish would be appreciated!
