5 Replies Latest reply on May 13, 2013 7:20 AM by Shawn Wallwork

    FILTER AND REFRESH

    Cosimo De Candia

      Hi All,

      I have a problem with refresh Dashboard with Calculate Field.

      I create this Variable:

      VAR1=

      if str(isnull([status]))="t" and [user_type]="Client" then "Prospect"

      elseif str(isnull([user_type]))="t" then "Prospect"

      else [user_type] end

       

      In VAR1 there are 2 possible value:

      Prospect and Client

       

      In User Type there are 3 possible value

      Prospect Client and Null

       

      If It Refresh a Dashboard, the VAR1 will have 3 possible value

      Prospect Client and Null

       

      If I create a new Variable VAR2 with same code of VAR1,this will have 2 possible value:

      Prospect and Client

       

      Why?

       

       

      Thanks in Advance,

      Cosimo

        • 1. Re: FILTER AND REFRESH
          Shawn Wallwork

          If you mock that up and post a packaged workbook I'll take a look.

           

          --Shawn

          • 2. Re: FILTER AND REFRESH
            Cosimo De Candia

            Hi S,

            in attach there are a csv,twb,tde

             

            Thanks in advance,

            Cosimo

            • 3. Re: FILTER AND REFRESH
              Shawn Wallwork

              Cosimo, this is definitely one for tech support. This shouldn't be behaving this way. I just duplicated your var1 field and here's what I got:

               

              Bug.png

               

              I can't think of anything you could do to a field to cause this behavior. I'll be interested in what they say.

               

              --Shawn

              • 4. Re: FILTER AND REFRESH
                Jonathan Drummey

                This is not a bug per se, it's a combination of the order of operations, the STR() function returning different results depending on data source, and an inefficient calculation that triggered that cascade of effects.

                 

                In the extract, the var1 calculation was materialized by the extract using Microsoft JET. When JET does the STR(ISNULL([status])) evaluation, it returns 1 or 0, however the var1 calculation is expecting that combination of functions to return "t" or "f", so the var1 calc returnst only the user_type for every row. This would appear to be incorrect results. I set up an example of STR(ISNULL([status])) in the attached to show this.

                 

                In the extract, the duplicated var1 (copy) calc is evaluated in Tableau, where STR(ISNULL([status])) returns "t" or "f", so the var1 (copy) calc is evaluating those as written, and I'm assuming that's the correct results. That's the behavior that Shawn was seeing.

                 

                The ISNULL() function returns True or False and that can be evaluated in an IF statement, so a simpler version of the var1 variable would be:

                 

                IF (ISNULL([Status]) AND [user_type] =="Client") OR ISNULL([user_type]) THEN

                     "Prospect"

                ELSE

                     [user_type]

                END

                 

                That returns the same results in both the live connection and the extract, see the var jtd calculated field.

                 

                (How I knew too look for this is from past experience with the STR() function returning different results depending on the data source, when I saw that in the calc to evaluate a boolean I thought something might be up there).

                • 5. Re: FILTER AND REFRESH
                  Shawn Wallwork

                  Interesting, I hadn't considered the difference between Jet and T. Good to know. Thanks,

                   

                  --Shawn