# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Limit Rows based on Upper and Lower Limits on Expressions

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

1 Solution

Accepted Solutions
MVP & Luminary

## Re: Limit Rows based on Upper and Lower Limits on Expressions

Hi,

Try like this

Dimension: AccountType, ID

Expression: = Sum(Aggr(If(sum(Income)>1200, Sum(Income)), AccountType))

Hope this helps you.

Regards,

Jagan.

21 Replies
Contributor

## Re: Upper and Lower Limits\Boundaries on Expressions

Try this:

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

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

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

Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

try

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

Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

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.

Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

maybe you leave out these [ ] ??

MVP & Luminary

## Re: Upper and Lower Limits\Boundaries on Expressions

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

talk is cheap, supply exceeds demand
Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

Tried even that below, it doesn't work.

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

Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

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?

Not applicable

## Re: Upper and Lower Limits\Boundaries on Expressions

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 ?

Not applicable

## Re: Upper and Lower Limits on Expressions

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

Community Browser