Help me fix this simple calculated field

Question & Answer
, , , ,
1

Here is what my raw data looks like:

Payment Method ID Payment Method Name Amount
1 CashAmt 5000
-1 Change -4000
2 Apply Pay 10000
3 Card Machine 10000
4 Online 10000

To show this to the users, I need to make sure the “Change” amount is deducted from the “CashAmt” amount to show how much our CashAmt sales were.

The Table in the dashboard should be:

Payment Method ID Payment Method Name Payment Amount
1 CashAmt 1000
2 Apply Pay 10000
3 Card Machine 10000
4 Online 10000

Here is how I tried to do it:
(Payment AMount Calculated Field)

ifelse({PAYMENT_METHOD}= 'CashAmt',
     sumOver( ifelse({PAYMENT_METHOD} = 'CashAmt', AMOUNT,0),[],PRE_FILTER)
     +
     sumOver( ifelse({PAYMENT_METHOD} = 'Change', AMOUNT,0),[],PRE_FILTER)
     
     ,
sumOver( AMOUNT,[{PAYMENT_METHOD},{PAYMENT_METHOD_ID}],PRE_AGG)
)

and then on the table, I added a filter to hide {PAYMENT_METHOD_ID} = -1 which is the “Change”. however, this makes the value in “CashAmt” not adhere to the other filters like country, date, branch, etc.

and if I change the “PRE_FILTER” to “PRE_AGG” it works as long as “Change” is not filtered out from the table. if I exclude “Change” from the table then “CashAmt” will just show its original value which is “5000” which is not what I wanted.

What is the best way to resolve this?

2

Hi,

is your data just an example and you have more of these “Change” rows?
IF not:

You can use ifelse, to rename “Change” to CashAmt and the Id -1 to 1

ifelse({PAYMENT_METHOD} = ‘Change’,‘CashAmt’,{PAYMENT_METHOD})

ifelse({PAYMENT_METHOD_ID} = -1,1,{PAYMENT_METHOD_ID})

So you create two new fields and use them for the table. This way the values of CashAmt and Change will be aggregated on CashAmt.

3

Thanks your solution works,