2 Replies Latest reply: Feb 23, 2017 8:42 AM by Cotiso Hanganu RSS

    Doubt In Qlik Sense

    Gulshan Rohilla

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

        • Re: Doubt In Qlik Sense
          Jonathan Dienst

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

          • Re: Doubt In Qlik Sense
            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