6 Replies Latest reply: Feb 24, 2016 1:57 PM by Sunny Talwar RSS

    Count by a Sum

    Derek Hutchinson

      I am trying to count retailers up who are greater than 250000 in net sales but I’m not sure how

       

      Count({<...Where net sales is greater than 250,000>}[Customer No_])

       

      The "Net Sales" is a calculation that is listed below

       

      Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])

       

      Can I Count the results of a "SUM"

       

      Thanks

        • Re: Count by a Sum
          Sunny Talwar

          May be this:

           

          Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 250000"}>}[Customer No_])

          • Re: Count by a Sum
            jagan mohan rao appala

            Hi,

             

            Try like this using set analysis

             

            =Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 250000"}>} DISTINCT [Customer No_])


            OR you can also try like this using Aggr() like below


            =Sum(Aggr(If(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])  > 250000, 1, 0), [Customer No_]))


            Regards,

            Jagan.

              • Re: Count by a Sum
                Derek Hutchinson

                Thank you this worked for what I was trying to do

                 

                Additional question if I wanted to add a RANGE. example customers between 150,000 and 250,000. How would I do that?

                 

                Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) < 250000"}>} DISTINCT [Customer No_]) 


                and

                 

                Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 150000"}>} DISTINCT [Customer No_])