1 2 Previous Next 24 Replies Latest reply on Sep 10, 2017 7:35 AM by Shinichiro Murakami

    LOD Expression

    Saket Metkari

      Hi Team,

       

      I am new to tableau and need your help in LOD Expression.

       

      Please find attached workbook.

       

      IF you go to Components Dashboard -

       

      I need addition of -

       

      Work Center - Labor Hours + Rework + Billable by Per week

       

       

      For Ex - Week 28 ->                     656 + 9 + 223,4 = 888.4 ,   I need this for every week in one table.

       

      Can you please help ?

       

      Thanks !

      Saket

        • 1. Re: LOD Expression
          Jim Dehner

          Hi

          your data is ver complex and I am confused with your goal

          you alread have a formula for billable hours as

                    SUM([Labor]) +

                     SUM([Raw Data (CAT2 Data)].[Total quantity]) +

                    SUM([Rework])

           

           

          And you want a formula for      Labor + Rework + Billable Hours

               it looks like you are double counting Labor and Rework

           

          also on the surface there does not appear to be a reason that you would need and LOD expression - can you explain the need -

          It will be an issue because all the data fields in must come from the same data source - your Billable Hour calculation above does not meet that criteria

          Jim

          Let me know if this helped

          • 2. Re: LOD Expression
            Saket Metkari

            Hi Jim,

             

            Sorry for not being precise -

             

            I have 3 sources -

            Production-Components

            Production-Systems

            CAT2 Data

             

            I have to create certain formulas and using it, create some charts.(Attached Screenshot)

             

            FYI -

            Production-Components

            Production-System                                               CAT2 Data - half data is for Components and half is for Systems (Depending Upon the cost centers)

             

            Now I will explain the formulas in detail -

             

            Please consider only 2 sheets as of now -

            CAT2 Billable Hours and Components dashboard -

             

            Source -                        Production-Components + Production-Component + CAT2 Data

             

            I need addition of ----> Work Center labor Hours + Rework + CAT2 billable Hours

             

            For instance -

            Week 28 -            656 + 9 + 7  = 672

             

            The problem is addition of data from 2 different sources. (I need to show in chart on a weekly basis)

             

            Thanks !

            Saket

            • 3. Re: LOD Expression
              Saket Metkari

              Hi Jim,

               

              My problem is this is not working -

               

              you alread have a formula for billable hours as

                        SUM([Labor]) +

                         SUM([Raw Data (CAT2 Data)].[Total quantity]) +

                        SUM([Rework])

               

              As in CAT2 my answer depends upon 2 filters -

              Source Object and Order Type

               

              if Order type is billable and source object - 6046502AV =  CAT2 Billable..

               

              Billable Hours = labor + rework + CAT2 Billable

               

              Thanks !

              Saket

              • 4. Re: LOD Expression
                Deepak Rai

                I have not seen your workbook but if you are joining all your datasources then Yes You can use LOD but if you are blending then the aggregation would break.

                Thanks

                Deepak

                • 5. Re: LOD Expression
                  Saket Metkari

                  Hi Deepak,

                   

                  Thanks for your reply.

                   

                  Can you please have a look in the workbook and let me know, I am a bit confused.

                   

                  Thanks a lot !

                  Saket

                  • 6. Re: LOD Expression
                    Shinichiro Murakami

                    SAket,

                     

                    I looked into workbook. But I was pretty confused.

                    Your explanation is not enough.

                    For example, I cannot see any number of 656,9  or 7

                    In week 28.

                     

                     

                    What is the key field you want to connect between all three data?

                     

                    Emp vs Personnel ID

                    Team vs Team

                    Order type vs Billable

                     

                    What is the relationship of Source object with other two sheets??   etcetcetc

                     

                    Thanks,

                    Shin

                    • 7. Re: LOD Expression
                      Saket Metkari

                      Hi Shin,

                       

                      656 comes from source - Production-Components , please check screenshot. (Component Dashboard)

                      9 comes from source - Production-Components,please check screenshot. (Component Dashboard)

                       

                      7 comes from CAT2 source - Screenshot- Week 28 (Here it depends upon filter - Source Object - 6046502AV and Order Type - Billable)

                       

                      Formula for Billable Hours is - Work Center labor Hours + Rework + CAT2 Billable

                       

                      I have attached all 3 excel sheets, sorry I am not much familiar with tableau.

                       

                      I want to connect with Employee Name and Name.

                       

                      Thanks you for your support and help !

                      Saket

                      • 8. Re: LOD Expression
                        Shinichiro Murakami

                        Saket,

                         

                        You confused us/yourself with below relationships.

                        First two sheets has two "week"s , one is based on real date and other is based on something different..

                        The sheet CAT 2 also has field of "week' but it's different from "week' of real date which your individual table shows.

                         

                        To get 672 on Week 28, you need to create another field like Week ID based on real date and link..

                         

                         

                         

                         

                         

                        On both sheets of component and System

                         

                        Link CAT 2 with other two sheets only with Week And Week ID.

                         

                        If I link with Name and Employee Name as you said, the number becomes different than 672.

                         

                         

                         

                        Thanks,

                        Shin

                        1 of 1 people found this helpful
                        • 9. Re: LOD Expression
                          Saket Metkari

                          Hi Shin,

                           

                          Thank you so much, this helps a lot. And sorry for the confusion.

                           

                          Can you please help me with below formulas, I have to submit this dashboard on Monday.

                           

                          Please check dashboards marked with Red.

                           

                          Hours not booked = SC Available Hours - (Billable + Non-Billable + Sickness)    Ex - Week 28 -    1114 - (672+97+32) = 313

                           

                          Actual Utilization ratio = Billable Hours / SC Available Hours                          Ex - Week 28 - 672/1114 * 100= 60.32%

                           

                          Non-Billable-Not Booked = Non-Billable + Hours not booked / SC Available Hours    Ex - Week 28 - 94+313/1114 * 100 =36.5%

                           

                          Sickness = sickness hours / SC Actuals              Ex - 32 / 1114 * 100 = 2.87%

                           

                          And with certain graphs.(Word File)

                           

                          Thanks !

                          Saket

                          • 10. Re: LOD Expression
                            Shinichiro Murakami

                            Saket,

                             

                            Request is too much with very poor information with very tight schedule.

                            Please clarify very in detail "Each word" is representing which field of which data source"

                             

                            Even with that , I am no sure I or someone really can meat your tight schedule request,.

                             

                            Thanks,

                            Shin

                            • 11. Re: LOD Expression
                              Saket Metkari

                              HI Shin,

                               

                              Apologies.

                               

                              I want to create calculated fields for these formulas - (Please see attached screenshot)

                               

                              I have this sheet in excel and want to achieve same results in tableau. I have sheet for every functionality for instance Billable, Sickness, Non-BIllable.

                               

                              Using these results I have to create calculated fields. (Attached Excel pwd - Wun01)

                               

                              Hours not booked = SC Available Hours - (Billable + Non-Billable + Sickness)    Ex - Week 28 -    1114 - (672+97+32) = 313

                               

                              Actual Utilization ratio = Billable Hours / SC Available Hours                          Ex - Week 28 - 672/1114 * 100= 60.32%

                               

                              Non-Billable-Not Booked = Non-Billable + Hours not booked / SC Available Hours    Ex - Week 28 - 94+313/1114 * 100 =36.5%

                               

                              Sickness = sickness hours / SC Actuals              Ex - 32 / 1114 * 100 = 2.87%

                               

                              Thanks !

                              Saket

                              • 12. Re: LOD Expression
                                Saket Metkari

                                Hi Shin,

                                 

                                My problem here is, I need total of Billable hour in one calculated field then I can perform the calculations.

                                 

                                The problem is it depends upon 2 filters . Order type-Billable and Source Object-604653AV

                                 

                                SUM(IF( [Order Type] = "Billable") THEN [Hours] END) ---- using this I do not get results.

                                 

                                If you could just help me with this, it would help.

                                 

                                Thanks !

                                Saket

                                • 13. Re: LOD Expression
                                  Shinichiro Murakami

                                  Again, it's not clear to me at all which data source's which field are you talking about.

                                  And excel requires password and cannot open.

                                   

                                   

                                  What is "Hours" / "SC available Hours" ????

                                   

                                   

                                  Shin

                                  • 14. Re: LOD Expression
                                    Saket Metkari

                                    Hi Shin,

                                     

                                    PLease find all screenshots..---

                                     

                                    If you see Billable Hours sheet which you created in screenshot. Ex- (Week 28 - 672)CAT2 source

                                     

                                    SC Available sheet - Production-Components source -----Ex - (Week 28 - 1114)

                                     

                                    NonBillable sheet - CAT2 source -----Ex - (Week 28 - 94)

                                     

                                    Sickness sheet - Production-Components source ---- Ex- (Week 28 - 32).

                                     

                                    Please only consider sheets marked with RED.

                                     

                                    Using this I want to create calculated field -

                                     

                                    HOurs not booked - SC Available - (Billable + NonBillable + Sickness )

                                     

                                    Thanks !

                                    Saket

                                    1 2 Previous Next