6 Replies Latest reply: Mar 11, 2011 2:51 PM by Brent Edwards RSS

    Expression Syntax

      Hello,

      I have two functioning equations:

      # of Businesses =floor(pct*count(DISTINCT ims_org_id))

      Cumulative Sales =sum( {$<ims_org_id={'=rank(sum(acct_brndddd_2010)) = 10'}>} acct_brndddd_2010)

      I need to replace the "10" in the Cumulative Sales expression with the # of Businesses expression.

      I have tried replacing the "10" with labels and other expression syntax, but I have not found the correct combination.

      Thanks for your help in advance!

      Brent

        • AW:Expression Syntax
          Martina Brenner

          Hi,

          create a new variable with your # of Businesses:

          varNoBusiness: =Floor(pct*Count(DISTINCT ims_org_id))

          and change in your expression the 10 into $(varNoBusiness)

            • AW:Expression Syntax

              Tried this, but the expression only produces zero as a result...

               

               

              =

              sum( {$<ims_org_id={'=rank(sum(acct_brndddd_2010)) = $(testvar)'}>} acct_brndddd_2010)





                • AW:Expression Syntax
                  Oleg Troyansky

                  Purely from the syntax perspective, - you are using single quotes, while you need double quotes to signify "search".

                  However, I think there is a deeper problem in trying to select ims_org_id and at the same time, to try and count the same field in your condition. I suspect that your count always returns 1 because it should be evaluated separately for each ims_org_id.

                  If you could explain the logic of your condition - what are you trying to accomplish, perhaps someone could offer a better solution

                    • AW:Expression Syntax

                      The count of the ims_org_id seems to be working okay.

                      Ultimately I am trying to produce a chart that compares the % of Customers to the % of Sales...so I could see if 20% of my customers were driving 80% of my business. The chart I want to create would have one dimension (Percent - predefined points where we want to see the % of Sales...my requirement is 1%,5%,20%,50%...these values are loaded within an inline view) and % of Sales (which requires the Cumulative Sales expression as the numerator).

                      The Cumalative Sales expression is trying to determine the Sales for the top n% of customers. For example:

                      # of customers = 1000 and total sales =2,000,000

                      20% of my customers =200

                      Rank the customers by Sales descending and sum the Sales values for the first 200 customers (lets say that equals 1,000,000)

                      So % of Sales would equal 1,000,000 / 2,000,000 = 50%

                       

                       

                       

                        • AW:Expression Syntax
                          Oleg Troyansky

                          I'd recommend creating a calculated dimension based on advanced aggregation function aggr - something like this:

                          AGGR(sum(Sales)/sum(TOTAL Sales) , CustomerID)

                          You'd need to add logic to distribute those numbers into your desired buckets, if you can't settle on equally distributed 10%, 20%, 30% etc...

                          Then your expression could simply be this:

                          count(distinct CustomerID) / count(total distinct CustomerID)

                          Use "Full Aggregation" to get cumulative totals.

                          You don't really need Set Analysis for this type of chart...