I'm trying to write an expression that looks at the # of returns and the # of sales for each part number, then calculate the sum only if the difference is >0. I also have a column indicating if it is a return or a sale.
I can get it to work when the part number is a dimension by using:
if(Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty])>0,Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty]))
(CR is the column that identifies if the record/row is a [CR]Return or [Sales]Sale.)
Example: If part xxx had 5 returns, 10 sales, the difference would be -5. I need this to be omitted from the overall total.
If part yyy had 10 returns, 5 sales, then the difference would be 5. I need this to be included in the overall sum.
I can't get an overall sum of the records that are >0 without including the part number as a dimension. It gives me a sum, but adds all of the returns and sales, then subtracts the sales from returns.
I've worked with this for quite some time, inserting [Part number] hoping it would perform the calculation for each row. It simply sums both categories (CR and Sales) and gives me the difference.
=if(Sum({< [Part Number] = ,[CR] = {'CR'} >} [Qty])-Sum({< [Part Number] = ,[CR] = {'Sales'} >} [Qty])>0,Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty]))
How do I get an expression to work with each record/row without using a dimension, then include only the records/rows that meet the criteria in another expression? What am I missing or doing wrong?
