Doubt In Qlik Sense

[Anonymous]

i have a dataset of debtors in which i have a column of Billing YTD consists both Invoice amount and credit notes raised(in negative). so i want to separate the values of both the credit notes and invoice amount. can anyone help me in this..

[jonathandienst]

Depends on where you want to use the separated values. As expressions you could use

Sales (positive values)                 =Sum(RangeMax([Billing YTD], 0))

Credit notes (negative values)       =Sum(RangeMin([Billing YTD], 0))

[cotiso_hanganu]

A pretty cool approach is to remember some simple algebra:

f(x) = (x + abs ( x) ) / 2 gives you x, if x >= 0 and zero, if x <0

f(x) = (x - abs ( x) ) / 2 gives you x, if x < 0 and zero, if x >= 0

=>

(sum( BillingYTD) + fabs( sum( BillingYTD) ) ) / 2  => will give you the invoices

(sum( BillingYTD) - fabs( sum( BillingYTD) ) ) / 2  => will give you the credit notes