3 Replies Latest reply: Nov 8, 2017 3:36 PM by omar bensalem RSS

    Use variable to filter

    Sinziana Lelescu

      I want to use only blank values from a field to filter the other data. But because I don't have values for those cells to select them, I created an If statement to insert value 1 on blank fields:

       

      =if (isnull([asp_date_end_FRS]),1,asp_date_end_FRS)

       

      If I use this expression I receive want I need and is ok (see below second field). I created a variable with this expression because I want to use it to filter other data (charts, tables) (see below the first field).

       

      The issue is when I use the variable with the same expression as above I receive only the value "1" for all fields. I know I'm doing something wrong and I would like to know what

       

      variable filter.jpg

        • Re: Use variable to filter
          omar bensalem

          Why variable? Variable only retain one value at time.

          Create a new dimension (a master item) as you've done and filter by it

            • Re: Use variable to filter
              Sinziana Lelescu

              I want to use the result of the calculated field as part of a second expression and from I know I can't use a master dimension on a expression. For example:

              SUM(if (isnull([asp_date_end_FRS]),1,asp_date_end_FRS)Value),   I just want to add values if the cell on "asp_date_end_FRS" field is blank. Maybe you can point to another solution to select only blanks and add other fields based on this selection?

                • Re: Use variable to filter
                  omar bensalem

                  I'd try to do it in the sccript:

                   

                  example, I have a field dim1 where I have some null values:

                   

                  Table:

                  Load *, dim1 as YourNewDim from source where dim1<>'' or dim1<> '-'  or len(trim(dim1))> 0 //dpends on how ur null values are


                  concatenante

                  load * ,'1' as YourNewDim from source where dim1='' or dim1= '-' or IsNull(dim1) or len(trim(dim1))= 0 ;

                   

                   

                  and use YourNewDim in your analysis !

                  Hope this helps