Limit Rows based on Upper and Lower Limits on Expressions

[#1]

Hi,

Is it possible to limit the rows based on limits on expressions? For example, I have sum(employees), sum(income), sum(tax) in my expressions and I want only those records to be displayed in my table where:

1. sum(employees) > 200 and sum(employees) < 500

2. sum(income) > $500 000 and sum(income) < $1000000

3. sum(tax) > $10 000

Is it possible to display only those records that fulfills upper and lower limits to above expressions ?

Thanks,

Sheetal

[don_qlikview]

Try this:

Sum({<Employee = {">=200 <= 500"} >} [Employee])

Sum({<Income = {">= 50000 <= 1000000"}>}[Income])

Sum({<Tax = {">=10000"}>}[Tax])

[#3]

try

sum({<employees = {">=200<=500"}>}employees)

[#4]

Hi Don,

Thank you for your reply. However, I tried the below in the expression but it does not give any output. It shows 0 for Number of Employees.

sum({<[EMPLOYEES] = {">=200 <= 500"} >}[EMPLOYEES])

Thanks,

Sheetal.

[#5]

maybe you leave out these [ ] ??

[Gysbert_Wassenaar]

If your three conditions should apply to all three expressions try this:

Variable:

• vLimits:  sum(employees) > 200 and sum(employees) < 500 and sum(income) > 500000 and sum(income) < 1000000 and sum(tax) > 10000

Expressions:

1. if( $(vLimits) , sum(employees) )
2. if( $(vLimits) , sum(income) )
3. if( $(vLimits) , sum(tax) )

If each condition should apply only to one expression try these three expressions:

1. if(sum(employees) > 200 and sum(employees) < 500 ,  sum(employees) )
2. if(sum(income) > 500000 and sum(income) < 1000000, sum(income) )
3. if(sum(tax) > 10000, sum(tax) )

[#7]

Tried even that below, it doesn't work.

sum({<EMPLOYEES = {">=200 <= 500"} >} EMPLOYEES )

[#8]

Thanks Gysbert for your reply. I tried for one of the condition. Added a variable called vEmpLimits with value =sum(EMPLOYEES) > 200 and sum(EMPLOYEES) < 500 and modified the expression to if( $(vEmpLimits) , sum(EMPLOYEES) ) but it does not show any value for that field in the table. Can you help?

[#9]

Hi Gysbert ,

I modified my variable to have only part of the condition sum(EMPLOYEES) > 200. It shows values now wherever the Sum(Employees) is greater than 200. However, remaining rows not having sum(EMPLOYEES) > 200 are also displayed. This is similar to if condition applied in expression. But, I want to display only those rows where sum(EMPLOYEES) > 200

Is this possible ?

[#10]

Hi Sheetal,

Requirement 1: You need to limit your expressions.

Solution: The one given by Gysbert and others covers it all.

Requirement 2: You need to display only those rows where conditions are satisfied

Solution: Use a calculated dimension.

Go to properties--> dimension tab of the chart and click 'Add calculated Dimension'.

Use this expression.

=If( Aggr (sum(EMPLOYEES), Your_Dimension_name)>10000, Your_Dimension_name)

Give it suitable name.

Check mark 'Suppress When value is Null' check-box.

You may add additional conditions as per requirement.

Warm Regards,

Kalpesh