Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Tried this, but the expression only produces zero as a result...
=
sum( {$<ims_org_id={'=rank(sum(acct_brndddd_2010)) = $(testvar)'}>} acct_brndddd_2010)
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
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%
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...
Not sure I follow your response...
Where does the rank come into the formulas you showed?
What do you mean by "add logic to distribute those numbers into your desired buckets"?