topic Re: Sum values in table with different transaction types in App Development

Sum values in table with different transaction types

mikegrattan — Wed, 15 Oct 2025 21:55:44 GMT

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

Re: Sum values in table with different transaction types

robert_mika — Thu, 16 Oct 2025 03:32:20 GMT

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

or/ and use the below

Re: Sum values in table with different transaction types

marcus_sommer — Thu, 16 Oct 2025 09:53:58 GMT

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

Re: Sum values in table with different transaction types

Or — Thu, 16 Oct 2025 10:23:14 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Thu, 16 Oct 2025 14:25:47 GMT

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?

Re: Sum values in table with different transaction types

mikegrattan — Thu, 16 Oct 2025 14:26:42 GMT

The Reason Code only applies to CO doc type transactions.

Perhaps an Aggr function is needed for the SO quantities?

Re: Sum values in table with different transaction types

Or — Thu, 16 Oct 2025 14:38:45 GMT

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.

Re: Sum values in table with different transaction types

marcus_sommer — Thu, 16 Oct 2025 14:39:05 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Thu, 16 Oct 2025 14:46:53 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Thu, 16 Oct 2025 15:22:19 GMT

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.

Re: Sum values in table with different transaction types

Or — Thu, 16 Oct 2025 16:21:42 GMT

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.

Re: Sum values in table with different transaction types

marcus_sommer — Fri, 17 Oct 2025 06:01:26 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Tue, 21 Oct 2025 17:11:50 GMT

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

Re: Sum values in table with different transaction types

marcus_sommer — Wed, 22 Oct 2025 09:27:37 GMT

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;

Re: Sum values in table with different transaction types

mikegrattan — Wed, 22 Oct 2025 22:37:35 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Wed, 22 Oct 2025 22:46:22 GMT

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:

Re: Sum values in table with different transaction types

marcus_sommer — Thu, 23 Oct 2025 06:16:23 GMT

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.

Re: Sum values in table with different transaction types

mikegrattan — Thu, 23 Oct 2025 18:10:24 GMT

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