topic AW:Expression Syntax in QlikView

Expression Syntax

Fri, 11 Mar 2011 16:58:54 GMT

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

brenner_martina — Fri, 11 Mar 2011 17:18:27 GMT

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

Fri, 11 Mar 2011 17:49:56 GMT

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 — Fri, 11 Mar 2011 18:18:40 GMT

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

Fri, 11 Mar 2011 18:58:56 GMT

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 — Fri, 11 Mar 2011 19:16:48 GMT

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

AW:Expression Syntax

Fri, 11 Mar 2011 19:51:27 GMT

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"?