Discussion board where members can get started with Qlik Sense.
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..
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))
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