In this chart, I need to combine (calculate) for a net total if the number in the RMA/PO# column and the Vendor Inv column match. This is an accounts payable function. So on the same check number there in an invoice and a debit and I want to combine these two lines for a Pmt Amt that would equal $240.42 as the information from this chart is exported into the accounting software. This would eliminate repetitive tasks for the employee.
How would I write an expression that would do this?
Message was edited by: Jennie Elliott
I have added an example of what my end results should be
Attached is what I am trying to achieve:
If RMA/PO# = Vendor Inv, then Sum for a Net Payment (Invoice - Credit). I need to combine these matching lines into one line if those numbers match.
Do I script this or write an expression?
Here is a variant using a calculated dimension (could be done in the load script thus removing the need for a calculated dimension), a pivot table, and partial sum on the [Date Entered] field. The expression is simply
Sum([Pmt Amt]). Have attached a sample app too.
The first table is just a Table Box to illustrate the raw table. The second table is a pivot with all dimensions expanded. The third table is just a copy of the second to show how it looks like when you "Collapse All" on the first dimension giving you hopefully exactly what you want your users to export to Excel...
Or if you prefer to do it in a load script so you actually get two new fields you can even do the Export via an Action/External/Export which demands to have fields with values to be able to export - I have also attached the lates QVW with the code/app: