We have an ancient Business App. We have salespeople who get credit for split sales based on a percentage. To calculate Invoice Sales, I sum the line items and then multiply the invoice total by each salesperson percent and our data balances fine.
A line of data might look like this
| Order# | Salesperson | Percent | InvoiceTotal | SalespersonTotal |
|---|
| XYZ | | 50 | 10,000 | 5,000 |
| XYZ | Salesperson2 | 50 | 10,000 | 5,000 |
When we want to goto the item level detail we get a bad join
sum({ $< SalesType={'SLS'}, SalesCommissionFlag={'Y'},Date_InvoiceDate={">=$(=date(iDateBeg))<=$(=date(iDateEnd))"}>} (Prod_SKU_SalesTotal *(Percent/100)))
| Order# | Salesperson | Percent | Product | Product Total | SalespersonTotal |
|---|
| XYZ | Salesperson1 | 50 | A | 4,000 | 4,000 |
| XYZ | Salesperson2 | 50 | A | 4,000 | 4,000 |
| XYZ | Salesperson1 | 50 | B | 6,000 | 6,000 |
| XYZ | Salesperson2 | 50 | B | 6,000 | 6,000 |
It seems like it is doing this because it is totaling the line item as 8,000 then applying the percent calculation. If I add DISTINCT before the calculation it seems to work but causes problems elsewhere (like when I don't want detail and drop the salesperson dimension)
sum({ $< SalesType={'SLS'}, SalesCommissionFlag={'Y'},Date_InvoiceDate={">=$(=date(iDateBeg))<=$(=date(iDateEnd))"}>} DISTINCT (Prod_SKU_SalesTotal *(Percent/100)))
| Order# | Salesperson | Percent | Product | ProductLineItemTotal | SalespersonTotal |
|---|
| XYZ | Salesperson1 | 50 | A | 4,000 | 2,000 |
| XYZ | Salesperson2 | 50 | A | 4,000 | 2,000 |
| XYZ | Salesperson1 | 50 | B | 6,000 | 3,000 |
| XYZ | Salesperson2 | 50 | B | 6,000 | 3,000 |
If I put in a Sum Table. All totals fine.
If I put in Pivot Table, Sum totals are wrong
Very complicated, please see attached.
