SUM vs PU Sum confusion

Question

Hi Gurus,

I am trying to create a PQL KPI for a View that will sum a field called TRX_rev_amount. Its a negative number so I'm using the ABS function.

For some reason, it is working great when I have it in charts or as a KPI list at the header level (see attached screenshot), but it does not work when I have it in a table. TBH I have no idea how it is coming up with the amount of $3.14k. It should be the same as above ($524). The code I'm using for the KPI is:

SUM( FILTER_TO_NULL(ABS("ar_billing_transactions"."trx_rev_amount")))

If I change the code to a PU_SUM (see below) with a domain field to aggregate it by the trx_number it will work for the table, but then does not work in the header or in charts etc. What should I be doing differently?

PU_sum (

DOMAIN_TABLE ( "ar_billing_transactions"."trx_number" ) ,

( ABS ("ar_billing_transactions"."trx_rev_amount" )

))

TRX Revenue amounts

Anonymous · Answer

Hi Timothy,I see 2 potential issues:SUM() is an aggregation, adding up all absolute values in your dataset. In contrast, PU_SUM sums up all absolute values per record in your domain table, i.e. per trx_number. So, if you want to get the same output you might want to wrap a "regular" aggregation around it, e.g. a SUM or AVG. I would recommend aligning the calculations - if possible of course. Since PU-functions aren't taking filters into account, including FILTER_TO_NULL() will make it filter aware. Could it be that the 3.14K is the cumulative value for this trx_number if you ignore all filters? For SUM, using FILTER_TO_NULL shouldn't make any difference. If you want to test the current setup without changing the formulas, selecting a single trx_number and removing all other filters might do the job. If that works I recommend aligning the formulas as much as possible.Hope this helps, good luck! Lucas

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN