3 Replies Latest reply: Aug 29, 2016 4:30 AM by robertrwb robertrwb RSS

    Count function

    robertrwb robertrwb

      Hello Everyone,

       

      I need Your help for function Count that will show the number of Customers with positive deviation in sales between current year and last year. My expression for the calculation of this deviation is       

       

      Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today()))"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])-Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today())-1)"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])

       

      Robert

        • Re: Count function
          balraj ahlawat

          may be like this?

           

          =count(distinct if(DeviationExpression>0, CustomerID)

            • Re: Count function
              Toni Kautto

              The nested aggregation might be a problem when adding the entire deviation aggregation inside the count.

               

              It depends a bit where you want to aggregate the count. In a table column you can simply use a total function to find the count based on a if() statement. For a calculated total I would suggest looking at a Aggr() based expression. See attached QVF for example on the principle.

               

              With the assumption that your dimensions is CustomerID, the expression could be something like:

               

              =Count( Aggr( if( Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today()))"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])-Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today())-1)"},Month={"$(=Month(Today())-1)"}>}[Volume sqm]) >0,1), CustomerID))

            • Re: Count function
              robertrwb robertrwb

              Toni,

               

              thanks a lot! It's works

               

              Robert