11 Replies Latest reply on Jun 12, 2017 2:47 PM by Saket Metkari

    Dynamic adding column

    Saket Metkari

      H Team,

       

      Please find attached excel file.

      I have thousands of lines data in excel, what I want is we have different product lines, in different region every product line has its Hours and field Techs updated every month.

      Ex. Product Line     Hours Available     No if Field Techs       Date

              Lunar                         45                         23                 Jan

              Seifert                        55                         25                 Feb

      I have to add columns like Hours available and No of Field Techs as per calculations, which is different every week, at present I am adding this columns in excel and achieving results

      But in long run it is not feasible,  How to achieve this in tableau?

      Please help !

       

      Thanks,

      Saket

        • 1. Re: Dynamic adding column
          mara.quindoza

          Hello Saket Metkari,

           

          The columns that you are adding, are they manual inputs or calculated ones?

          If they are calculated ones, we can create calculated fields in Tableau.

           

          Thank you.

          • 2. Re: Dynamic adding column
            Saket Metkari

            Hi Mara,

             

            please find attached packaged tableau file, I need the data in this way in tableau, as of now I am creating columns in excel and then extracting to tableau.

             

            But using calculated fields how to achieve for every product line

            • 3. Re: Dynamic adding column
              mara.quindoza

              Hello Saket Metkari,

               

              How is these two fields calculated though? I can see they are not part of the actual Excel model. Are they manual inputs?

               

               

              If they are manual inputs, there is no way we can add it in Tableau if the values differ for every line.

              Can you confirm? Thanks!

              • 4. Re: Dynamic adding column
                Saket Metkari

                Hi Mara,

                 

                The 2 columns Hours and percentages are fine, hours I get them directly from SAP and Percentages using Calculated field.

                 

                for instance How I calculate here it is:-

                 

                Service Manager         Date        No of field techs               hours available

                Aaron                          Jan                    4                              720 (No of working days * Working Hours daily * No of FSE)

                Aaron                          Feb                    5                              780 (Depending upon No of FSE Count)

                Juergen                       jan                    16                             1600 (Same formula as above)

                 

                This is the case for every month, the FSE number may change and depending upon that hours available.

                So this is for each region.

                 

                At present I am entering this values in excel(no of Field techs and hours Available).

                 

                How to do this in tableau ?

                 

                Mara your help is much appreciated. Thanks.

                 

                Thanks !

                SAKET

                • 5. Re: Dynamic adding column
                  mara.quindoza

                  Hello Saket Metkari,

                   

                  We can create a calculated field as long as the fields are present. Where do you base the # of working days and working hours daily.

                  It is a little bit confusing for me because I can't find the FSE in the Excel file.

                  Also row 2 formula is different from row 1? Is that right?

                  • 6. Re: Dynamic adding column
                    Saket Metkari

                    Hey Mara,

                     

                    Thanks a lot for replying me.

                     

                    I came to the conclusion as well to create a calculated field, but how ? here i need your help please.

                    FSE is No of Field Techs, sory for the confusion,

                     

                    The Hours available formula is different for every region like Aaron and Juergen in above case

                    For instance, Aaron - Hours Available formula (22*8*4)

                                          Juergen - Hours Available (22*7*16)

                     

                    So how to create a calculated field for every region but everything should be in same column, only this I need.

                     

                    Awaiting for your reply.

                     

                    Thanks !

                    SAKET

                    • 7. Re: Dynamic adding column
                      Saket Metkari

                      Hey Mara,

                       

                      Any solution for this, I am doing a lot of manual work here and need some solution ASAP.

                       

                      Please do help.

                       

                      Thanks,

                      SAKET

                      • 8. Re: Dynamic adding column
                        mara.quindoza

                        Hello Saket Metkari,

                         

                        Apologies for the late response as I'm busy with other important stuff.

                        I understand the urgency. So to clarify the logic is Hours Available = (# of working/business days for that month)*(# of hours worked)*(# of field techs)

                         

                        In that case, it might be easier to create a formula in Excel dynamically for the # of working/business days for that month using the NETWORKDAYS function where the start date is the start of the month and the end date is the end of the month. We can also do it in Tableau but it will be more complex. You can refer to the link here just in case - https://www.interworks.com/blog/jbajon/2015/11/10/questions-tableau-training-business-day-calculations.

                         

                        For the # of hours worked since you have several managers or people, it is best to have it present in the raw data in Excel maybe through creating a lookup table instead of doing a lengthy IF statement in Tableau.

                         

                        And then once you have these two fields, we can create a calculated field in Tableau with the ff. logic: (# of working/business days for that month)*(# of hours worked)*(# of field techs). It will be a row by row calculation in that case. If in case you wanted to aggregate the # of field techs per month and name, we have to tweak it to create an aggregated calculation.

                         

                        I will be on the go and traveling so I will try to respond as much as I can.

                        Hope this helps a little. Will try to send a sample file in a while

                        1 of 1 people found this helpful
                        • 9. Re: Dynamic adding column
                          Saket Metkari

                          Hi Mara,

                           

                          Thanks a lot for answering me from your busy schedule.

                          The thing is while counting working days I also need to exclude public holidays.

                           

                          The other thing is:

                          And then once you have these two fields, we can create a calculated field in Tableau with the ff. logic: (# of working/business days for that month)*(# of hours worked)*(# of field techs). It will be a row by row calculation in that case. If in case you wanted to aggregate the # of field techs per month and name, we have to tweak it to create an aggregated calculation.

                           

                          Can you please send me a sample file for this logic in tableau ?

                           

                          Thanks a lot for your precious time.

                           

                          Regards,

                          SAKET

                          • 10. Re: Dynamic adding column
                            mara.quindoza

                            Hello Saket Metkari,

                             

                             

                            I am assuming you are going to include the # of field techs in the view.

                             

                            A row by row calculation will calculate it for every row in your dataset. Say our formula is Field Hrs*Hours Available*# of days, it will return that output for every row in your dataset. When you dragged it in the view, you have the option to choose how to aggregate it whether it is a sum or an average, etc.

                             

                            An aggregated calculation on the other hand is for example our formula is SUM([Field Hrs])*AVG([# of Hours])*AVG([# of days]), then for whatever fields in your view, it will already be aggregating it. When you drag it in the view >> it will say AGG([Calculated Field]) as compared to a row by row level calc where you can change the aggregate.

                             

                            For the # of days per month, I am thinking you may use the NETWORKDAYS function in Excel already to make it easier. You can indicate the public holidays from there so it won't be accounted for.

                            1 of 1 people found this helpful
                            • 11. Re: Dynamic adding column
                              Saket Metkari

                              hey Mara,

                               

                              Sorry for late reply I was out station.

                               

                              Thanks a lot it worked in excel but i am still facing issues in tableau.

                              I will try to figure out, but if not possible then will get back to you.

                               

                              Thanks !

                              SAKET