21 Replies Latest reply: Sep 26, 2014 1:32 AM by Sheetal Panchal RSS

    Limit Rows based on Upper and Lower Limits on Expressions

    Sheetal Panchal

      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
          Sohail M

          Try this:

           

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

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

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

          • Re: Upper and Lower Limits\Boundaries on Expressions

            try

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

            • Re: Upper and Lower Limits\Boundaries on Expressions
              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) )
              • 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

                  • Re: Upper and Lower Limits on Expressions
                    Sheetal Panchal

                    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 mohan rao appala

                        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: Limit Rows based on Upper and Lower Limits on Expressions

                          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
                              Sheetal Panchal

                              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 mohan rao appala

                                  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
                                      Stefan Kunte

                                      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
                                        Sheetal Panchal

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

                                          • Re: Limit Rows based on Upper and Lower Limits on Expressions
                                            jagan mohan rao appala

                                            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.

                                              • Re: Limit Rows based on Upper and Lower Limits on Expressions
                                                Sheetal Panchal

                                                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,