topic Re: Set Analysis in App Development

Set Analysis

LoKi_asterix — Fri, 08 Aug 2025 14:01:32 GMT

Hey everyone!

I’m looking to convert a Sum(If) expression into a set analysis format.

Sum(
If(IsNull( %TransCode ) or Len(Trim( %TransCode )) = 0
or
(Len(Trim( %TransCode )) > 0 and (IsNull([Transaction Type]) or Len(Trim([Transaction Type])) = 0)),
$TransAmtLocal
)
)

Re: Set Analysis

adilio_silva — Fri, 08 Aug 2025 14:13:29 GMT

I think something like:

{< %TransCode = {'*'} , [Transaction Type] = {'*'} >} Sum($TransAmtLocal)

Re: Set Analysis

howdash — Mon, 11 Aug 2025 19:16:28 GMT

What I tend to do in these situations is transfer that IF statement to the script and create a new field in the data model, like this:

tableName: LOAD *, If(IsNull( %TransCode ) or Len(Trim( %TransCode )) = 0 or (Len(Trim( %TransCode )) > 0 and (IsNull([Transaction Type]) or Len(Trim([Transaction Type])) = 0)), 1, 0) as forTransAmtLocal Resident originalTable ;

Once you have this new forTransAmtLocal field (or whatever you want to call it), your expression can be as simple as this:

Sum({<forTransAmtLocal = {1}>} $TransAmtLocal)

Re: Set Analysis

BrunPierre — Mon, 11 Aug 2025 18:00:36 GMT

I get that you want where the transaction code or transaction type is missing, then perhaps

=Sum({1-$<%TransCode = {'*'}> + 1-$<[Transaction Type] = {'*'}>} $TransAmtLocal)