5 Replies Latest reply: Nov 27, 2014 12:15 PM by Kevin Bertsch RSS

    Hiding rows based on condition

      I have a straight table with dimensions and expressions.  I am trying to hide the entire row if the amount = 0

      Under the presentation tab, for each of the columns, I have a conditional statement - IF(GL_AMOUNT>0, expression)

      and the suppress zero values & suppress missing is also checked.

      The row is still showing up.

      Is there something I'm missing???

      Any other suggestions?????

      Here's what the screen looks like.  As you can see from prod_order 123057, there are two cost_price 260 & 261 that have 0 for gl_amount.  I would like to hide these rows.

       

       

        • Re: Hiding rows based on condition
          Gysbert Wassenaar

          That conditional statement is to show or hide the entire column. That won't work for what you want to do. You need to add the if clause to every expression. Or use set analysis expressions; for example sum(NO_OF_UNITS) would become sum({<GL_AMOUNT={'>0'}>} NO_OF_UNITS).

            • Re: Hiding rows based on condition

              On the expressions tab, I had checked the conditional button and placed the if condition there.

              I moved the if condition to the definition for the expression and that worked.

              Thank you!!!

              • Re: Hiding rows based on condition

                I have a slightly different issue. We import parts from various countries. Each part has a code, a vendor, and a tariff treatment, depending on where the vendor made/bought the part. For one part code, there can be many different tariff treatments from the same vendor.

                 

                We want a table that shows ONLY cases where one part code from one vendor has two or more tariff treatments.

                It is sorted by Part Code, then Vendor.

                e.g.

                Part  vendor   TT  Price

                1A     XYZ      1    5

                1A    XYZ       2   5.5

                1A     ABC     1    5

                2A    ABC      4    6

                2A     DEF     2    6

                3B    ABC     1    10.5

                3B    ABC     4    11.2

                3B    DEF     3    9.8

                 

                In this example, we would want the output to be:

                Part Vendor TT Price

                1A    XYZ     1  5

                1A    XYZ     2  5.5

                3B   ABC     1  10.5

                3B   ABC     4  11.2

                 

                I tried to do this using the ABOVE function, but it apparently doesn't work on dimensions, only expressions.

                But when I tried it on the expressions, I didn't get the results I wanted either For example, I created in my script

                a "PV" field, which was simply the concatenation of Part and Vendor, and put in the formula (expression)

                =if(PV=Above(PV,1),PV,0) However, this gave me all the rows, even if the Part # was different from the row above.

                 

                Can anyone shed some light on this? Thanks!

              • Re: Hiding rows based on condition
                Stefan Wühl

                The conditional hide / show statements work only per chart, not per row.

                 

                To hide a row, you need to activate 'supress zero values' on presentation tab and then make sure, that all expressions return zero for that row, e.g. by checking for the result of GL_Amount:

                 

                =if(sum(GL_AMOUNT) >0, Only(POSTING_DATE),0)

                 

                for the POSTING_DATE column expression. To help you more, you need to post more details about your dimensions and expressions used.

                • Re: Hiding rows based on condition
                  Mark James

                  Are you sure that the value is actually 0? If the value was 0 then it would actually display $0.00. You should actually test using the IsNull() function, and choose to only display values where the values are not null.

                   

                  Alternatively, if these values are not need anywhere in the application, then you could restrict the load within the LOAD statement, doing

                   

                  LOAD

                  fieldA,

                  fieldB,

                  GL_AMOUNT

                  where isNull(GL_AMOUNT) <> 0

                   

                  I hope this helps. Please mark as answered and helpful if so.