1 2 Previous Next 23 Replies Latest reply on Dec 7, 2016 9:24 AM by Yuriy Fal

    LOD with Table Function

    Bernd Langer

      Hi Tableau-fellows,

       

      I’ve got the following use case:

      The products of a company (machines) could be returned for repair.

      For each repair incident you’ll find the following data fields

      - Repair ID

      - Date Ship (Date of first delivery to the client)

      - Date Repair IN (Date of return)

      - Date Repair OUT (Date of delivery after repair)

      To analyze all repair incidents we’d like to see the following:

       

      1) Functioning Time in Table View

      For each repair incident we’d like to know the time (days) for how long the product worked well in the client’s factory.

       

      2) Filter out of „Regions“

       

      3) Bar Chart with frequencies

      For each category based on intervals of functioning time (see 1), we’d like to know the overall frequencies (with the possibility to filter out repair incidents)

       

      I succeeded with 1) (see attached Worbook, Worksheet „Tab“ with CalcFields "FunctTime" and subcalcs „Date Repair OUT pred“ and „Date Repair IN - MIN“)

      But as this solution is based on a table function (in „Date Repair OUT pred“), I failed to achieve 2) and 3).

      It looks pretty much as using LODs but I couldn’t found a solution to solve the conflict between table functions and LOD.

       

      Any help would be much appreciated

       

      Best regards from Germany

       

      Bernd

        • 1. Re: LOD with Table Function
          Shinichiro Murakami

          Bernd,

           

          Could you try below on both sheets?

           

          Edit Table calc and set "Restarting "

           

           

          Thanks,

          Shin

          • 2. Re: LOD with Table Function
            Bernd Langer

            Hi Shin,

             

            thanks for your prompt answer !

             

            With your solution I partly achieve my objectives, but the following remain:

             

            2) Filter out of „Regions“
            If I filter out regions, the calculation of FUNCTIONING TIME gets wrong.

             

            3) Bar Chart with frequencies

            If I filter out REGION or SEGMENT the bars get white stripes.

            IT would be better if the bars would be completely filled.

            Another wished featrure would be the possibility to create stacked bars adding REGION or SEGMENT on color.

             

            Thanks

            Bernd

            • 3. Re: LOD with Table Function
              Shinichiro Murakami

              So, only the table calc is

               

              Could you specify how this table calc should be?

              Re-satart every customer is my current understanding, but re-start at combination of "Customer x Region" ?

               

               

              Thanks,

              Shin

              • 4. Re: LOD with Table Function
                Bernd Langer

                Hi Shin,

                 

                this table function takes the DateRepairOUT from the repair incident one row above the actual (sort order: DateRepairIN). Even if I would filter out this row it should get this DateRepairOUT.

                Is this possible e.g. with a FIXED LOD ?

                 

                Best

                 

                Bernd

                • 5. Re: LOD with Table Function
                  Shinichiro Murakami

                  Thank you for the clarification Bernd

                   

                  Let me keep investigating.

                   

                  Thanks,

                  Shin

                  • 6. Re: LOD with Table Function
                    Yuriy Fal

                    HI Bernd,

                     

                    I would like to help, too,

                    though I have only partial answers

                    to both your questions.

                     

                    2) As for filtering Regions and Segments,

                    the (partial) solution is using Table Calc filters as

                    LOOKUP(ATTR([Dim]),0) computed along Cell.

                     

                    This effectively hides (not removes) Marks on a view,

                    so other Table Calculations don't break.

                     

                    The caveat is that Table Calc filters

                    couldn't be applied across views.

                     

                    3) As for a Frequency Histogram,

                    the (partial) solution is using counting

                    (and filtering) by RANK functions.

                     

                    The caveat here is that one couldn't apply filters

                    on other Dimensions -- either Regular or Table Calc ones.

                    Filtering by the latter doesn't change the result in this case.

                     

                    So to count the number of Repair IDs in a particular Bin (say <5Jr)

                    one could be filtering on a Table view (Sheet 7 Tab)

                    and looking for a number of Marks at the lower left.

                     

                    Hope it could be of help.

                     

                    Yours,

                    Yuri

                    • 7. Re: LOD with Table Function
                      Bernd Langer

                      ... many thanks, Yuri !

                       

                      It completely solved my issue.

                       

                      Bernd

                      • 8. Re: LOD with Table Function
                        Yuriy Fal

                        Bernd, you're welcome.

                        • 9. Re: LOD with Table Function
                          Bernd Langer

                          Hi Yuri,

                           

                          calculations are still OK, but after assembling the table and chart in a dashboard, I realized I cannot apply the Filters (made by LOOKUP-Fct) to other sheets within the dashboard.(he option is not available in the context menu)

                           

                          Attached you'll find the mockup.

                           

                          Bernd

                          • 10. Re: LOD with Table Function
                            Bernd Langer

                            ... another topic:

                             

                            In our client's data set, the two fields used by the calculation for the bar chart are of different data types:

                            "Repair ID" (number)

                            "Customer Name" (string)

                             

                            Now, the two table calculations "# repair ID (filter)" and "# repair ID (size)" don't work anymore...

                            • 12. Re: LOD with Table Function
                              Yuriy Fal

                              Hi Bernd,

                               

                              1) From my answer to the Q2 above:

                              The caveat is that Table Calc filters

                              couldn't be applied across views.

                              That's exactly the limitation you've encountered.

                              The solution / workaround? Not any at the moment.

                               

                              Generally, if one is able to accommodate filtering only single value from a Dim,

                              then Parameters could help, but they can't when multi-selecting.

                               

                              Luckily, in the mockup the [Segment] dimension could be used directly

                              as a Dimension Filter, since every Customer is in a single Segment.

                               

                              But the [Region] dimension couldn't be used as a Dim Filter,

                              since it breaks the [Functioning Time] Table Calculation

                              when a Customer has Repair IDs belonging to different Regions.

                              That's why Table Calc filtering comes into play in this case.

                               

                              2) From my answer to the Q3 above:

                               

                              The caveat here is that one couldn't apply filters

                              on other Dimensions -- either Regular or Table Calc ones.

                              Filtering by the latter doesn't change the result in this case.

                               

                               

                              3) Having Repair IDs as numbers (either Integer or Float no matter what)

                              is not a problem. When changing pills on a view with Table Calculations,

                              it is wise to look at the Addressing / Partitioning for the latter ones

                              by choosing Edit Table Calculation dialog on each of Table Calc pill

                              (there are two in this case, one on Rows and another one on Filters)

                              and making the necessary adjustments. I've changed it in your recent wb,

                              but you could do it, too, by looking in the previous wb and making the same.

                               

                              Please note that both [# Repair IDs (Size)] and [# Repair IDs (Filter)]

                              are Nested Table Calculations. They include the inner Table Calc part --

                              the [Date Repair OUT pred] which has different Addressing / Partitioning

                              then the outer (main) Table Calculation.

                               

                              Please find the attached workbooks.

                              Hope it could help.

                               

                              Yours,

                              Yuri

                               

                              PS That's why I've said that only partial answers are given.

                              • 13. Re: LOD with Table Function
                                Bernd Langer

                                Hi Yuri,

                                 

                                thnaks for your quick, patient and extensive reply.

                                 

                                Unfortunately I am not able to completely reproduce your solution (see sheet bar2).

                                I supect the problem on the "# Repair ID (filter)": in your solution I can move the slider of the visualized filter up to 19xx whereas in mine the slider is fixed to 1.

                                 

                                Greets

                                 

                                Bernd

                                1 2 Previous Next