Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
Yes. Check this qvw
Check this
Hi Sheetal,
Try this:
Expressions:
1. = Sum(Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))
2. = Sum(Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID))
Regards
KC
I guess you can remove SUM in the beginning also.Try both.
1. = Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID)
2. = Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID)
Regards
KC
Thanks Anbu. These were very helpful and works with the above sample data and expressions. However, if I apply these to my another sample that has optional upper and lower limits 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.
I have attached the sample here. The optional lower and upper limits are working for both columns. If I apply your solution, the AND condition works but it suppresses all rows with 0 values in any of the column even when the limits are selected or not selected
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)))))
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))))))