2 Replies Latest reply: Nov 20, 2012 5:15 AM by Syed Khaled Shahbaaz RSS

    expression help

      hi all,

       

      I have a straight table with an expression ..

      =

      IF(iv_fiscal_year = $(vCurrentYearFiscal),$(vTargetSum_Volume))

       

      i have supress null values ticked on a dimension..

      =

      IF(it_target_type='Volume',it_target_type)

       

      and i get a total figure on the expression of

       

      9182

       

      I want this to be displayed in a text box too but when i add the expression into a text box i get a value of

       

      9582

       

      this is because it is also counting the NULL values too.

      is there a way i can exclude null values in the next box also so i get the correct value of 9182?

       

      I think all i need to do is combine the 2 expressions above but not sure how..

       

       

      Thanks

        • Re: expression help

          Hi,

           

          Assuming you are counting/summing up values, you could use Null() function in the expression to eliminate null values. This would even work with the text box.

           

          It could be something like this

           

          =count(if(Dimesnion<>'',Value)) or =count(if(Dimesnion<>Null(),Value))

           

          Is that what you are looking for?

           

          -Regards,

           

          Khaled.

            • Re: expression help

              Hi,

              no, i think i originally asked the wrong question..

               

              i want to encorporate the 2 expressions together..

               

              IF(iv_fiscal_year = $(vCurrentYearFiscal),$(vTargetSum_Volume))

              and

              IF(it_target_type='Volume',it_target_type)

               

              I have tried

               

              IF(

              it_target_type='Volume',iv_fiscal_year = $(vCurrentYearFiscal),$(vTargetSum_Volume))

               

              but i get the same value as just using the first expression on its own.