12 Replies Latest reply on Feb 15, 2019 10:20 AM by Jonathan Drummey

    Further on Color Code Again

    Faiz MH

      Hi Tableau Experts,

       

      This is based on a previous problem I had. Sorry that I had to hilite again due to some new development. I had tried but still could not find the answer. The new developement is that there is additional requirement. For example for both Malindo and ThaiAir. Instead of previously labelled as 'No Tested', now I have to label it as 'Not Require'. I had tried the following and does not seems to work as expected. Can I know what shall I do to have 'No Require' in the legend.

       

       

      Tagging Mr Mahfooj Khan  and Mr Shinichiro Murakami. Perhaps you guys can give me your ideas.

       

      Thank you..

        • 1. Re: Further on Color Code Again
          Shinichiro Murakami

          It may be do-ale with table calc, but way too much complicated.

          Better start prepare clean data with eliminate null.

           

          Thanks,

          Shin

          • 2. Re: Further on Color Code Again
            Chris McClellan

            So you don't want to simply filter it if you don't want to see it ?

            • 3. Re: Further on Color Code Again
              Faiz MH

              Yes, I don't wanna simply filter it. I would like to see No Require, No Tested, No Data and Data OK at the Legend.

              • 4. Re: Further on Color Code Again
                Shinichiro Murakami

                See attached twbx, excel and join structure.

                 

                Thanks,

                Shin

                1 of 1 people found this helpful
                • 5. Re: Further on Color Code Again
                  Faiz MH

                  Hi Shin-san,

                   

                  Thanks! This is great with the multiple joins. However, in my actual case I am getting the data from a database and then I need to publish the dashboard to the web. I am still thinking what other way I can do this one

                  • 6. Re: Further on Color Code Again
                    Shinichiro Murakami

                    Anyways, the point is eliminating "Non--existing" cell with adding row with Null for all combination.

                     

                    Thanks,

                    Shin

                    • 7. Re: Further on Color Code Again
                      Faiz MH

                      Jonathan Drummey, do you mind to help.

                      Thanks.

                      • 8. Re: Further on Color Code Again
                        Jonathan Drummey

                        I've got an option for you, however this requires using Tableau's data densification feature that I personally try really hard to avoid in production workbooks because it is so sensitive. I'd much rather do what Shin outlined, namely pad (densify) the data as part of the data source. With Tableau's cross-database join features it's often possible to set this up, for example the Product & Parametric tables could come from Excel, Custom SQL on your database, etc. and then join the data from your database to those results.

                         

                        In the current view the reason that your calculation is failing is due to an order of operations issue, here's what Tableau is doing:

                         

                        1) Tableau computes record level calculations and regular aggregates in the data source. In this view this means getting the dimensions on Rows and Columns, SUM(Cnt Qty), and ATTR([Product]) (the latter being used in Calculation4).

                        2) If one of the densification criteria are met then Tableau pads out the data. In this view the Calculation4 with a compute using of Table (Across) triggers densification. In this case that means that Mailindo/SYSTEST1 is padded out.

                        3) Any fields from 1) that are densified get a value of Null. This means that ATTR([Product]) for Mailindo/SYSTEST1 is imputed to Null, *not* Mailindo.

                        4) Table calculations complete evaluation. At this point Calculation4 is fully computed, and since ATTR([Product]) is Null per #3 that means the result of NO REQUIRE never occurs.

                         

                        So to work around this I made the following changes:

                         

                        - Added a Padded Product calculated field with the formula TOTAL(MIN([Product])). With a compute using of Table (down) it has a non-Null value in every cell

                        - Changed the Calculation4 to use the Padded Product field instead of ATTR([Product])).

                        - Made sure that the nested compute using was accurate.

                         

                        v2018.1 workbook is attached.

                         

                        Jonathan

                        2 of 2 people found this helpful
                        • 9. Re: Further on Color Code Again
                          Faiz MH

                          Thank you very much Jonathan for your time.

                           

                          I have tried what was suggested by Shin-san and it actually works! However, my SQL query data will keep refreshing every few days and sometime certain so called product data, parametric and/or status data won't appear as they are not available. This makes it not suitable to portray that in my dashboard as this will paint a not so real picture.

                           

                          I have just tried your method in my real query data and noticed that it did not work as expected. Example, instead of showing "No Require", it goes to "Data OK". It seems that the calculated field did not "recognized" the SYSTEST1 lines and instead went straight down to ELSE "Data OK".

                           

                          I am sure if this due to limitation in Tableau. I have been trying to figure it for quite sometime already

                          • 10. Re: Further on Color Code Again
                            Jonathan Drummey

                            re: the scaffolding method that Shin proposed - in that case you’d need to make sure that each of the scaffold sources was using the latest data.

                             

                            re: the nested calculation. If the calculation is not working then most likely you have not correctly configured the nested compute usings. Feel free to post a packaged workbook and I can take a look.

                             

                            Jonathan

                            • 11. Re: Further on Color Code Again
                              Faiz MH

                              Jonathan, I'm sorry, in the previous reply I actually overlook at the Padded Product as I forgot to drag it to Columns(anyway, the output is about the same). Here is the correction and its result. This workbook is matched to the actual data but due to confidentiality purpose, some columns has been removed.

                               

                               

                              Please find the attached workbook.

                               

                              Thanks.

                              • 12. Re: Further on Color Code Again
                                Jonathan Drummey

                                I'm sorry I wasn't more clear on nested table calculations. Each pill in the view is independent of other pills (mostly). In the case of table calculations if I have two table calculations A and B, where B's formula uses A then how the compute using is set up depends on which  pill is added first:

                                 

                                - If I drag out A as a pill and set A's compute using, then drag out B, then B will inherit A's settings.

                                - Whereas if I drag out B first then the nested A calculation will use the default compute using setting for that pill (Table (down) or Table (across) across depending on the discrete dimension pill(s) on Rows and/or Columns), then if I drag out A as a pill and set that compute using it won't change nested instance of A inside B.

                                 

                                From your description it sounds like you did the latter. So to fix your view I did the following steps:

                                 

                                1. Right-click on the Calculation4 pill and choose Edit Table Calculation...

                                2. In the Nested Calculations drop-down choose Padded Product.

                                3. Change the Compute Using to Table (down).

                                 

                                Here's the view:

                                 

                                Screen Shot 2019-02-15 at 1.15.54 PM.png

                                 

                                v2018.1 workbook is attached.

                                 

                                One other note regarding nested table calculations like Calculation4. If you right-click on a pill and directly set the Compute Using that sets the compute using for *all* of the nested calculations used in that pill. So when using nested calculations we need to be aware of the direction (the compute using) we want for each calculation so we know whether we can use the right-click to set or need to use the Edit Table Calculation... option to configure the nesting and other options.

                                 

                                Jonathan