Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gulshanrohilla
Contributor

Doubt In 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..

2 Replies
MVP
MVP

Re: Doubt In Qlik Sense

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))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
cotiso_hanganu
Contributor

Re: Doubt In Qlik Sense

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