10 Replies Latest reply on Jul 29, 2016 2:39 PM by Mikey Michaels

    Calculated field hide/unhide measure

    Mikey Michaels

      I recently posed this question on the Tableau Forum and the only advice I received is creating two separate workbooks and combining them on a dashboard. Do I have any other options? I would like to keep a format where the percentages and order counts are in side-by-side as show below.

       

      Thank you for your time and help!

       

      Cheers,

      Mikey

       

       

      Hello All,

      Thank you so much for taking a looks at my question.

      Here's what I have: I created a calculated field that measures the utilization percentage of orders that went through our new order system called "EASy".

       

      This calculated field is called "new 3-month util- Current Month". To calculate the usage percentage for the current month, I hid a the values where EASy? = "No". The "new 3-month util- Current Month" calculated field gives me the correct usage percentages, however, when I add my next calculated field "IS ROW in range ?- Current Month (1)", I need both to calculate the number of records for both EASy = "YES" and "NO". Since I hid "No", I only get the records where EASy = "YES"

       

      Since I'm a notice Tableau user, I assume I need to adjust the way I calculate the "new 3-month util- Current Month"  percentage.

        • 1. Re: Calculated field hide/unhide measure
          Mikey Michaels

          any ideas before I close?

          • 2. Re: Calculated field hide/unhide measure
            khalid norat

            Just having a look at this. I think all you need is a level of detail calculation but let me have a quick play and get back to you.

            • 3. Re: Calculated field hide/unhide measure
              khalid norat

              Hi Mikey,

               

              Sorry a little confuse by what you are trying to achieve here.

               

              Please could you explain the purpose and the proposed result ignoring any calulations you have already created.

               

              What are the figures you would like to see in the visual

              • 4. Re: Calculated field hide/unhide measure
                Mikey Michaels

                Thank you for your willingness to take a look, Khalid...much appreciated!

                 

                What I'm trying to accomplish is have the EASy Utilization percentage in one column and next to it have the total number of orders in the second column for each category. So for instance, when I look at the source data, the total EASy Utilization percentage for AMER is 99%, which is correct on the attached. However, since in my EASy Utilization formula, I hid the records that where EASy = "No" my second column is incorrect. This column should read 228 not 223.

                 

                 

                Unfortunately my company is very slow to upgrade to version 9 so I'm still stuck with 8. Is there anyway to solve in 8?

                 

                THANK YOU SO MUCH FOR YOUR TIME!

                 

                Cheers,

                Mikey.

                • 5. Re: Calculated field hide/unhide measure
                  khalid norat

                  Unfortunately you require use of the LOD formulas which were introduces in version 9 upwards and no equivalent is available for versions previous to tableau 9

                  • 6. Re: Calculated field hide/unhide measure
                    Mikey Michaels

                    Thanks for the reply.

                    Can you send instructions on how to use the LOD calculation so when my company upgrades I will have a solution?

                    Thanks!

                    • 7. Re: Calculated field hide/unhide measure
                      Joe Oppelt

                      You can do LOD-equivalent things in 8.x with table calcs. 


                      Let me take a look here.

                      • 8. Re: Calculated field hide/unhide measure
                        Mikey Michaels

                        Many thanks, Joe!

                        • 9. Re: Calculated field hide/unhide measure
                          Joe Oppelt

                          I'm not sure what I am seeing here...

                           

                          First of all, when you filter with a quick filter (as you have seen), Tableau ignores rows that get filtered out.  Totals, SUMs, etc., all use the subset of rows that remain after the filter has taken effect.

                           

                          But if you filter with a table calc, Tableau keeps the underlying table intact, and just DISPLAYS the cells that satisfy the table calc.

                           

                          So I created a Sheet 4.  And I made a table calc that does a LOOKUP for cells that have particular [Eula exception] values as you specify.

                           

                          (Do you notice the asterisk in the selection list?  That's generated from the "All" value that is created on the TOTAL rows.  We'll have to do something creative here if you choose to pursue this method.  If you turn off Totals/subtotals, the asterisk goes away.  But one step at a time...)

                           

                          Now play with that filter.  Unclick "NO", and all the "NO" rows disappear, but the totals still total properly.  The underlying rows are still there, so Tableau "sees" them and includes them in TOTAL.

                           

                          But ( and here is what I don't understand what I'm seeing ),  If I add that same [Choose EULA] calc onto your sheet, it doesn't show "YES" or "NO" in the valid list.  And I threw ATTR(EULA Exception) into TEXT, and nowhere do I see NO or YES.  I'm not sure why.  Somehow that sheet is generating ... I-don't-know-what ... for EULA Exception.  Not NULL, because we also get a choice of "Null" in the filter list.

                           

                          BTW, you can neaten up the way you presented your original sheet so that you don't have all the separate lines on the MARKS card by  using the MeasureNames/MeasureValues method.  See Sheet 5.


                          Anyway, my primary point was to show how a table calc filter can change your viz without eliminating actual rows in the underlying table.  you might know more about your data to know why you are getting " " for some EULA Exceptions in the sheet you have.

                          2 of 2 people found this helpful
                          • 10. Re: Calculated field hide/unhide measure
                            Mikey Michaels

                            Thank you so much, Joe! This is excellent!