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).

[Or]

If it is required as a dimension, you will need your SO formula to include something to ignore that. Otherwise, you won't get any results. Not sure why it's required as a dimension, though... typically in this scenario it's easier to make it a measure. That said, they're your requirements. 

[marcus_sommer]

Displaying the reason code information as expression should be workable for this table and maybe even necessary if there are more as a single code possible in regard to the aggregation-level - which may be then done per concat(ReasonCode, ' + ').

Another consideration may go to fill for all DocType the ReasonCode - maybe even in an extra field. Of course such measurements are needing more efforts (extracting missing information in beforehand and applying them with n mappings) but it could simplify the UI significantly.

[mikegrattan]

There isn't a number that differentiates the CO and the SO - they are both related to the same sales order number.

[marcus_sommer · Accepted solution]

Like hinted you just need to fill the SO records with the information from the CO records, for example with a mapping:

m: mapping load OrderID, ReasonCode from X where DocType = 'CO';

t: load *,
   if(DocType = 'SO', applymap('m', OrderID, '#NV'), ReasonCode) as ReasonCodeX
from X;

[mikegrattan]

I don't see how Reason Code would be anything else but a dimension - it's not something you can sum, avg, etc. because it's not a number and doesn't make sense as a measure.

[mikegrattan]

The reason code only applies to the CO transaction for the sales order. For example:

In this example, sales order (SO) 12345 originated on 1/1/2025, 500 units were sold for $2,000. On 2/15/2025, a credit (CO) was issued for $1500 and the reason code recorded was RJQ.

For our report requirements, we want to list the credit amount, $1500, and the original quantity sold, 500, on the same row. So it would look like this:

[marcus_sommer]

In this case the above hinted mapping would be part of the right approach to get the wanted information - whereby you may need n mappings to get all required information.

It's a quite common method whereby most of the advanced users will take other approaches like the detours with link-table data-models.

Some background - many ERP have fact-tables like orders + billing + shipment. Keeping these facts separately within a BI model leads to a lot of trouble because each one would need a connection to the dimensions which would result in synthetic keys and/or circular references between the facts/dimensions and/or they getting their own dimensions. Of course these challenges are solvable with a lot of extra efforts within complex UI expressions and synchronizing the selections - whereby it won't help by missing information and/or missing key-values between the facts (again solvable with calculated dimensions). Surely possible but it's rather a nightmare then a sensible solution.

Therefore are many using link-table data-models but like hinted it's mostly a detour because the concatenation of the facts could be done directly within a single fact-table. This avoids completely the challenges with linking the facts and dimensions.

But none of this will be enough to get such required information like described or to make an example again - the order-record of such fact-table will know it's OrderID but not a BillingID or ShipmentID. There are various scenarios thinkable to look from one fact-side to the others, like from the orders how many where already billed and similar which could be quite simple answered within a view if the order-records are filled with the existing BillingID.

Essentially is the understanding that it may need x extracting/filling loads to get all needed information and that's not mandatory a weakness of the BI tool and/or the data-model and/or the data-set to apply such measurements else it's often the most pragmatic way to derive the not directly accessible information.

Your single task might be easier solvable as re-designing the data-model with switching dimension and expression and/or using an aggr() logic but if data needs to ordered and/or accumulated and/or (date/amount) offsets calculated between orders and/or billings ... and these ones flagged/scored and/or missing records needs to be populated it would be necessary to mandatory to do this work within the data-model and not within the UI.  

[mikegrattan]

The mapping approach is the answer - works great. Thanks!