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

# Expression Syntax

Hello,

I have two functioning equations:

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.

Brent

Hi,

• ###### 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

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

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