3 Replies Latest reply on Nov 17, 2016 4:11 PM by Shinichiro Murakami

    Use data lookup to set calculated field?

    Louis G

      In the example included, I have a list of products, features and costs. With that I generate a tree map.  I set the color of the squares with a true/false value: does the reference product have this feature or not?

       

      Currently this reference product is determined before query time and the "is it in the reference product" field is computed in the query.  Check the data to see how the results are returned (sort by feature, it will be obvious). What I'd like is for the user to be able to change the reference product in the viz and have the colours change without having to query again. In essence, I'd like the "Reference product?" and "Feature in reference product?" to be calculated fields.  I'm on Tableau 10.

       

      I never found a way to do that. The closest I get is manually creating a SET of features and then dragging that on the color field but the users don't have access to the desktop version so they can't do that.  In any case, it's doable with such a small example, but the real data has 10,000+ rows with tons of combinations so it's not practical to do that even for me in the desktop version.

       

      Example enclosed.

       

       

      Thanks!

        • 1. Re: Use data lookup to set calculated field?
          Shinichiro Murakami

          Louis,

           

          I'm not sure I understand your request correctly not, but could you check my attached file.

           

           

          Create parameters added from both "Product" and "Feature".

           

          [Category by Param]

          If left([Select Item],5)="Featu" then [Product]

          elseif left([Select Item],5)="Gizmo" then [Feature Name]

          end

           

          [T/F by Param]

          If

          left([Select Item],5)="Featu" then

              (if {fixed [Product]:

              count(if [Feature Name]=[Select Item]

              then [Feature Name] end)}>=1

              then "Y"

              elseif {fixed [Product]:

              count(if [Feature Name]=[Select Item]

              then [Feature Name] end)}<1

              then  "N" end)

          elseif

          left([Select Item],5)="Gizmo" then

              (if {fixed [Feature Name]:

              count(if [Product]=[Select Item]

              then [Product] end)}>=1

              then "Y"

              elseif {fixed [Product]:

              count(if [Product]=[Select Item]

              then [Product] end)}<1

              then  "N" end)

          end

           

           

          Thanks,

          Shin

          • 2. Re: Use data lookup to set calculated field?
            Louis G

            Shinichiro,

             

            That is a serious ninja use of FIXED LOD!  You nailed it and it does exactly what I want.  In fact, it does more, because I do not need to be able to switch from Product to Feature, but that is really cool and could be very useful in the future.

             

            Just to be sure I understand correctly:

             

            could [T/F by Param] be simplified as:

             

            If

            left([Select Item],5)="Featu" then

                (if {fixed [Product]:

                count(if [Feature Name]=[Select Item]

                then [Feature Name] end)}>=1

                then "Y"

                else "N" end)

            elseif

            left([Select Item],5)="Gizmo" then

                (if {fixed [Feature Name]:

                count(if [Product]=[Select Item]

                then [Product] end)}>=1

                then "Y"

                else "N" end)

            end

             

            Or am I missing something?

             

            If not then is the following difference intentional or is it something you missed in a copy/paste code duplication?

             

            If

            left([Select Item],5)="Featu" then

                (if {fixed [Product]:

                count(if [Feature Name]=[Select Item]

                then [Feature Name] end)}>=1

                then "Y"

                elseif {fixed [Product]:

                count(if [Feature Name]=[Select Item]

                then [Feature Name] end)}<1

                then  "N" end)

            elseif

            left([Select Item],5)="Gizmo" then

                (if {fixed [Feature Name]:      **** [Feature Name] LOD here...

                count(if [Product]=[Select Item]

                then [Product] end)}>=1

                then "Y"

                elseif {fixed [Product]:         **** ... and [Product] LOD here.

                count(if [Product]=[Select Item]

                then [Product] end)}<1

                then  "N" end)

            end

             

            Thanks so much. I knew there had to be a way and I looked and looked.  But this is a very ingenious way to go at it.  Love it!

             

            Louis

            • 3. Re: Use data lookup to set calculated field?
              Shinichiro Murakami

              Louis,

               

              My formula is a result of trials and errors and I just stopped at the point I judges it worked.

              That means if you think you can simplify, you can try and verify.  That's your call.

              And of course, for future use, depend on how the feature and products name look like, you need to figure out common rule for feature and product respectively.

              According to your comment, you understand the logic, so from here, it's your journey. 

              Don't afraid, just trials and errors, that's just a fun not a difficulty.

               

              Hope you enjoy Tableau!

               

               

              Thanks,

              Shin