5 Replies Latest reply: Jan 26, 2011 5:42 PM by Neil Miller RSS

    Count function with non-NULL  and non-ZERO

      Hello,

      I have 5 SALESGROUPS each with a large number of CLIENTS.

      In the raw data some of the CLIENT'S REVENUE is either a

      1. positive/negativer number
      2. or hardcoded zero
      3. blank.

      I created a Straight Table with the SALESGROUP as the dimension and wanted only to COUNT THE NON-ZERO/NON-NULL CLIENTS.

      I tried:

      =if (revenue<>0 or revenue <> NULL(),count(distinct CLIENTS)) and some variants but not working.

      Can anybody advise? Thanks in advance!

       

       

        • Count function with non-NULL  and non-ZERO
          Neil Miller

          Nulls can sometimes be difficult to work with. I would try to replace those values in the load script with either a 0 or -, if you need to tell the difference between 0 and blank. You should also try using Set Analysis. This may work:

          REMOVED
          That should get rid of the nulls and zero values.

          EDIT: The expression pasted incorrectly. It has been removed. See below...

            • Count function with non-NULL  and non-ZERO

              Thanks for the reply.

              I tried that expression but the expression comes up as an error (expression is default black font after the single quote).

              I've never seen this type of expression before and am hoping there is some syntatical adjustment needed as I've no experience with the 0' type syntax.

              Otherwise, I'm going to try to make a script adjustment like you suggested. Thanks again.

                • Count function with non-NULL  and non-ZERO

                  Hi there, try this:

                  count($<revenue = {"*"} - {0}>distinct CLIENTS )

                  It is not tested but thats the main idea. The star ({"*"}) should get rid of null values, while (-{0}) should take the zero values out of the equation.

                  Regards

                    • Count function with non-NULL  and non-ZERO

                      Sorry, I forgot to mention that if you are using the revenue as an expression, similar to these:

                      sum(revenue)

                      You might try using as sum(aggr) type expression to count the values:

                      sum(aggr(if(sum(revenue) >0,1,0) ,Customer))

                      You can also use this notation to get rid of the if:

                      sum(aggr( (sum(revenue) > 0) * -1 ,Customer))

                      Regards

                    • Count function with non-NULL  and non-ZERO
                      Neil Miller

                      Yeah, sorry, I was having some issues with the site earlier. That is not the expression I pasted in. The Edit option wasn't working and I kind of forgot about it.

                      The expression I was trying to post was:

                      Count({<revenue={'>0'}>+<revenue={'<0'}>} distinct CLIENTS)


                      That should give the list of all CLIENTS with a revenue greater than 0 or less than 0, which should exclude nulls and zeros.