Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Dimension: AccountType, ID

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

Hope this helps you.

Regards,

Jagan.

View solution in original post

21 Replies
don_qlikview
Creator
Creator

Try this:

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

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

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

Not applicable
Author

try

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

Not applicable
Author

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
Author

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

talk is cheap, supply exceeds demand
Not applicable
Author

Tried even that below, it doesn't work.

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

Not applicable
Author

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
Author

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
Author

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