Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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