Sum values in table with different transaction types

[mikegrattan]

I have a table that contains two different types of transactions, credit orders ("CO") and sales orders ("SO").

I want to have a column that totals the CO dollars:

Sum({<DocType = {'CO'}>} ExtendedPrice)

And I want to have a column that totals the quantity originally sold:

Sum({<DocType={'SO'}>} ShippedQty)

The result I'm getting is incorrect. I'm seeing CO dollars of 0 when there are SO quantities > 0, and I'm seeing SO quantities of 0 when there are CO dollars >0. 

I would like to see dollars and quantities summarized for each line in the table, but only when a dollar amount exists for the CO transaction. Instead, it looks like I'm getting lines for SO quantities when there isn't a related total for CO dollars (and vice versa).

[robert_mika]

you may use if(Sum({<DocType = {'CO'}>} ExtendedPrice)>0 ....then your logic.

or/ and use the below

[marcus_sommer]

The reason may be a not suitable relationship between the involved data - especially if they come from different tables.

[Or]

I'm not quite following what the problem is. Looking at the screen capture, it looks like each line is correct. The reason you never have both quantities and dollars in the same line is because each line has different dimensions. In particular, the Reason Code dimension seems to split out the returns into different lines. 

[mikegrattan]

The data comes from the same table. The initial sales transaction has a doc type of SO but a credit can occur after that, so a new transaction will be added to the table with a CO doc type. The CO doc type can have a reason code that explains the reason for the credit.

My goal is to have one record per CO transaction for each Item Description, by Week and Year. On that same record I also want to show the sum total of the SO quantity for that item, in that week and year.

Perhaps an Aggr function is needed for the SO quantities?

[mikegrattan]

The Reason Code only applies to CO doc type transactions. 

My goal is to have one record per CO transaction for each Item Description, by Week and Year. On that same record I also want to show the sum total of the SO quantity for that item, in that week and year.

Perhaps an Aggr function is needed for the SO quantities?

[Or]

In that context, you would need to have something differentiating the CO transaction (ID or something similar) and then a set analysis to ignore the ID and Reason Code. You could also do that with aggr(), I think. 

[marcus_sommer]

For a quick check you could remove some dimensions from the table - maybe just to a single one like year or week to see if the price/quantity results are on a single row or not. And then adding the next dimension and so on until you get two rows again - which means you found the one which has not the right relationship.

Very helpful is usually a table-box with the related fields and without any aggregations.

[mikegrattan]

The Reason Code dimension is a requirement for the table on this sheet. I need to show the sum total of CO transactions in dollars for each Item Description by Week and ShipYear. You are correct that the SO transaction does not have a Reason Code and that could be why I'm struggling to get the correct SO quantity summed by the same dimensions, excluding the Reason Code.

[mikegrattan]

The If statement only gets me to a certain point - I do start to see more filtering overall and more data in the quantity column, but the quantities don't match what I'm seeing in my control table, which shows only the SO transactions for comparison.