Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have below two tables.
Invoice Table:
Dept | PO ID | invoiceID | invoiceamt |
Sales | A1 | IN1 | 5000 |
Office | A2 | IN2 | 2000 |
HR | A3 | IN3 | 1500 |
invoice Expense table:
invoiceID | %distribution |
IN1 | 20 |
IN1 | 10 |
IN1 | 30 |
IN1 | 10 |
IN2 | 100 |
IN3 | 100 |
i want to display the below detail
Dept | PO ID | invoiceID | invoiceamt | invoiceamt modified |
Sales | A1 | IN1 | 5000 | 3500 |
Office | A2 | IN2 | 2000 | 2000 |
HR | A3 | IN3 | 1500 | 1500 |
where i used (0.01* percentage distribution * invoiceamount) for each entry of the invoice ID in expense table to get the final modified amount.
how can i get the desired output.
May be try this
Dimensions
Dept PO ID invoiceID
Expression
Sum(invoiceamt) Sum(invoiceamt) * Sum(%distribution) * 0.01
May be try this
Dimensions
Dept PO ID invoiceID
Expression
Sum(invoiceamt) Sum(invoiceamt) * Sum(%distribution) * 0.01
Please find the macro file