topic Re: Upper and Lower Limits\Boundaries on Expressions in QlikView

Limit Rows based on Upper and Lower Limits on Expressions

Fri, 12 Sep 2014 17:28:28 GMT

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

Re: Upper and Lower Limits\Boundaries on Expressions

don_qlikview — Fri, 12 Sep 2014 17:35:37 GMT

Try this:

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

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

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

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 17:39:29 GMT

try

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

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 17:49:34 GMT

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.

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 17:53:39 GMT

maybe you leave out these [ ] ??

Re: Upper and Lower Limits\Boundaries on Expressions

Gysbert_Wassenaar — Fri, 12 Sep 2014 17:54:31 GMT

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:

if( $(vLimits) , sum(employees) )
if( $(vLimits) , sum(income) )
if( $(vLimits) , sum(tax) )

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

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

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 18:00:23 GMT

Tried even that below, it doesn't work.

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

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 18:02:58 GMT

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?

Re: Upper and Lower Limits\Boundaries on Expressions

Fri, 12 Sep 2014 19:42:26 GMT

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 ?

Re: Upper and Lower Limits on Expressions

Sun, 14 Sep 2014 19:39:12 GMT

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

Re: Upper and Lower Limits on Expressions

Mon, 15 Sep 2014 02:04:27 GMT

Thanks Kalpesh for your reply. I want to show only those records where sum(Employees) > 200 and sum(Employees) < 500. So I will need to use calculated dimension. I tried the following but it shows me "1" in this column for all rows even though sum(Employees) is less than 200. I have tried only for first condition sum(Employees) > 200

=If( Aggr (sum(EMPLOYEES), EMPLOYEES)>200, EMPLOYEES)

Thanks,

Re: Upper and Lower Limits on Expressions

jagan — Mon, 15 Sep 2014 02:44:00 GMT

Hi,

Try like this

Expression:

If(sum(employees) > 200 and sum(employees) < 500 and sum(income) > 500000 and sum(income) < 1000000 and sum(tax) > 10000, 'True')

Note: You can replace True in the above expression with the value of your wish.

Hope this helps you.

Regards,

Jagan.

Re: Upper and Lower Limits\Boundaries on Expressions

Gysbert_Wassenaar — Mon, 15 Sep 2014 07:58:56 GMT

The expression in the variable should NOT be starting with an = sign. Otherwise it will be evaluated outside the context of the chart.

Re: Upper and Lower Limits on Expressions

Mon, 15 Sep 2014 15:09:11 GMT

Thanks Gysbert !. However, I want to display only those rows fulfilling these conditions. I think calculated dimension can be used. I have tried below expression in calculated dimension, but it shows all rows with value 1 for all rows even those not fulfilling the condition.

=If( Aggr (sum(EMPLOYEES), EMPLOYEES)>200, EMPLOYEES)

Re: Upper and Lower Limits on Expressions

jagan — Tue, 16 Sep 2014 04:40:31 GMT

Hi,

Try like this

=Aggr (If(sum(EMPLOYEES) >200, EMPLOYEES))

Regards,

Jagan.

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

Tue, 16 Sep 2014 20:38:23 GMT

Hi Sheetal,

If haven't got this working yet, can you share few hundred rows of sample data in excel. Its much more easy to understand and provide the solution this way.

A note on this expression that you tried to use

If( Aggr (sum(EMPLOYEES), EMPLOYEES)>200, EMPLOYEES)

The Aggr function aggregates results of an expression (i.e. sum(EMPLOYEES)) against a dimension that comes after comma separator.

If you use Employees as the dimension, then the Aggr statement reads it as "Sum of Employees per Employees" which has to be one and that is what you are getting.

So it will help if you can share what are the other dimensions you are using in Chart. (not to forget the sample data in excel)

Regards,

Kalpesh

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

Thu, 18 Sep 2014 21:50:20 GMT

Thanks Kalpesh. I am using following sample data. I have created a straight table with following dimensions and expression.

Sample Data:

Load * Inline [

Period, ID, AccountType, Income

201401, 1, AAA, 100

201401, 1, BBB, 200

201401, 1, CCC, 300

201401,2, AAA, 200

201401,2, BBB, 100

201401,2, CCC, 400

201401,3, AAA, 300

201401,4, BBB, 400

201401,5, AAA, 100

201401,6, CCC, 200

201401,7, CCC, 100

201401,8, AAA, 500

201401,9, BBB, 100

201401,10,BBB, 900

];

Dimension:

1.AccountType

2.ID

3.=(If( Aggr (sum(Income), AccountType)>1200, Income)) with Suppress when value is Null checked

Expression:

1.Period

I am getting only 1 row in the output for AccountType BBB which is correct. But, I am getting Income displayed as 900. I want to show the sum(Income) instead of just Income. Hence, I want to display 1700 in a single row.

I also tried following:

1. =(If( Aggr (sum(Income), AccountType)>1200, Aggr (sum(Income), AccountType))) but this shows 5 rows each with 1700 value

2. =(If( Aggr (sum(Income), AccountType)>1200, Aggr (sum(Income), AccountType,ID))) but this shows 1 row with 200

Is this possible?

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

jagan — Fri, 19 Sep 2014 01:11:45 GMT

Hi,

Try like this

Dimension: AccountType, ID

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

Hope this helps you.

Regards,

Jagan.

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

Anonymous — Fri, 19 Sep 2014 04:15:33 GMT

HI,

ID DImension splits Income to 900. Skip ID DImension and then use the expression Jagan posted.

Best Regards

Stefan

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

Thu, 25 Sep 2014 16:53:13 GMT

Thanks Jagan. There is one change in the requirement. I have attached the sample file and output screenshot. I want to display only those rows that satisfies both the conditions (AND) instead of OR. i.e as per the expression applied, only one row satisfying both the condition should be displayed. Is this possible ?

Also, this is just a sample but in real scenario, I have about 10 such numeric columns that has lower and upper limits coming through variables whose values are entered by users

Data:

Sample_Data:

Load * Inline [

Company, ID, AccountType, No_Of_Emp, Revenue

A, 1, AAA, 28,300000

B, 1, BBB, 70,500000

C, 1, CCC, 80,450000

D,2, AAA, 200,2000000

E,2, BBB, 100,5000000

F,2, CCC, 70,750000

G,3, AAA, 300,6000000

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions:

1. = Sum(Aggr(If(sum(No_Of_Emp)>200 AND SUM(No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))

2. = Sum(Aggr(If(sum(Revenue)>200000 and sum(Revenue) < 3000000, Sum(Revenue)), Company,ID))