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

[jagan]

Hi Sheetal,

I didn't get you, if you looking for combining both the expressions then try below expression

= Sum(Aggr(If(sum(No_Of_Emp)>200 AND SUM(No_Of_Emp) < 500 AND sum(Revenue)>200000 and sum(Revenue) < 3000000, Sum(No_Of_Emp)), Company,ID))

= Sum(Aggr(If(sum(No_Of_Emp)>200 AND SUM(No_Of_Emp) < 500 AND sum(Revenue)>200000 and sum(Revenue) < 3000000, Sum(Revenue)), Company,ID))

Regards,

Jagan.

[#3]

Hi Jagan,

I have the expression with combined conditions working. However, if I apply these to my another sample that has optional upper and lower limits (conditions) applied to both columns and the limits controlled by variables whose values are entered by user input box, it suppresses all rows with 0 values in any of the column even when the limits are not selected which should not be the case. Can you help ?

I have attached the sample here. The optional lower and upper limits are working for both columns.

If you cannot open the file, the data, dimension and expressions used in this sample are (vEmpLower, vEmpUpper , vRevLower  and vRevUpper  are variables whose values are entered through input box)

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,11, CCC, 70,750000

F,3, DDD, 0,750000

G,3, AAA, 300,6000000

G,12, BBB, 300,0

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

K,14, CCC, 0,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions: - Working for optional limits but not AND condition

1.

if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp)))))

2.

if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue)))))

I have tried the following:

Expressions modified to: - Working for AND conditions and optional limits but it suppresses rows with 0 values when any limits are not selected

1.

IF(if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))),if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp))))))

2.

IF(sum_No_Of_Emp,if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))))

Thanks,