topic Problem using expression in Dimension Limits in QlikView

Problem using expression in Dimension Limits

jcarpenter9 — Wed, 25 Apr 2012 15:27:05 GMT

I have a chart with two dimensions (SalesPerson and Product Family) and the expression sum(LineSalesAmount). I want to use Dimension Limits to display only Product Family rows whose sum is less than the average of Product Family sales per SalePerson. When I enter an expression in the "Show only values that are..." window, it is ignored and the chart displays all Product Family rows.

I've attached a QVW example. The yellow chart is the one where I want to display sales less than the average. Look in the Dimension Limits tab for the various expressions I've tried - all but one is commented out. FYI, this is the QV Developer training app.

I appreciate everyone's feedback. I'm using this app to learn new QV 11 features.

Problem using expression in Dimension Limits

Wed, 25 Apr 2012 15:59:47 GMT

Hi James,

Your expression using aggr() does filter out rows that have sales < 185.017

Could you elaborate on the result you are expecting ?

Hilaire

Problem using expression in Dimension Limits

jcarpenter9 — Wed, 25 Apr 2012 16:08:13 GMT

I see your point - I missed that. That looks like it is filtering based on the app-level average, whereas I want it to filter at the SalesPerson level.

I'm expecting it to filter out rows at the SalesPerson level that have sales < that SalesPerson's average. For example, for Erik Presley, show only rows < 63,682. For Frank Roll, show only rows < 255,414, etc. Still not sure how to get there.

Problem using expression in Dimension Limits

chematos — Wed, 25 Apr 2012 16:11:11 GMT

Hi James,

you could try with a calculated dimension, something like this:

if(sum({$<[Product Family]=> LineSalesAmount)<(Avg({$<[SalesPerson]=> LineSalesAmount),[Product Family])

I haven´t tested the expression.

hope this helps

Chema.