1 2 Previous Next 17 Replies Latest reply on Jul 4, 2017 12:54 PM by Saket Metkari

    Calculations - Using trends

    Saket Metkari

      Hi Team,

       

      I have a scenario -

       

      I have to create trends for Billable Time, Warranty, Training and Travel using Months or Quarters (All into Percentages)

       

      The problem is calculations -

       

      Workbook - Trends (Billable)

       

      I have created a sheet here i want to see trends per Month into percentages (According to the Service Managers)

       

      It should show trends - only for Billable (So i tried to hide all other fields in Work Type) (I cannot deselect other field or it will take 100% for Billable)

       

      So, it shows me - 61% total, so when I want to see by Month it shows me different % (Which is wrong) (Here I don't want to filter I have to show trends )

       

      It should show me - Jan (55%), Feb (66%) and Mar (66%) and April (57%) and so on,  in this way I can see trends for each Month into Percentages.

       

      Can you please help, if any doubts please let me know.

       

      Thanks !
      SAKET

        • 1. Re: Calculations - Using trends
          Jim Dehner

          Hi

          there are 2 ways to go - you can hard code the "Billable" into the formula with

              (If attr([Work Type])="BILLABLE" then sum([Hours]) end)/sum({ FIXED (DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})

           

           

          Or you can fix the calculation by work type and then filter for Billable with

          ({ INCLUDE  [Work Type]: sum([Hours])})/({ FIXED (DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})

           

          The choice is based on what else you need - either way the results are

           

           

           

          Let me know if this helps

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculations - Using trends
            Saket Metkari

            Hi Jim,

             

            This helps a lot, (Just one more thing i have to show this percentages via Service Managers(They are from different regions))

             

            When I filter via Service Managers (Please see Screenshot) - Then it doesn't give proper results

             

            So for instance - let' say

            Aaron (UK) - Jan - 32%, Feb - 56%, Mar - 48%

            Frederic(SWE) - Jan - 67%, Feb - 79, Mar - 87%

            It should give me these results

             

            And so on.

             

            Really appreciate your help. thank you so much !

             

            thanks a lot !
            SAKET

            • 3. Re: Calculations - Using trends
              Saket Metkari

              Hey Jim, For more reference please find excel sheet.

               

              Thanks !

               

               

              SAKET

              • 4. Re: Calculations - Using trends
                Jim Dehner

                Hi

                Tokk a little bit to understand the calculation that you were trying to accomplish - the last question is for any given sm what is his % of billable to the total of all his hours.

                 

                The first calculation I gave you is the overall percent of billable hours to the total of all hours - in each case by month

                 

                That will change the denominator of the formula - the formula now becomes

                 

                     ({ INCLUDE  [Work Type]: sum([Hours])})/({ FIXED [Service Managers],(DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})

                 

                and your results become

                 

                Think of LOD functions as creating partitions and permutations for the dimensions that precede the colon :

                In this case in words the denominator would read - for each service manager and for each year/month sum the hours

                 

                Let me know if this helps

                Jim

                 

                • 5. Re: Calculations - Using trends
                  Saket Metkari

                  Jim,

                   

                  Thanks a ton, thank u very very much , it certainly helps a lot !

                  The thing is I am new to tableau and to be honest not good with LOD expressions as of now. (But trying to cope with this kind of calculations)

                   

                  Jim, Just one more thing please,

                   

                  For trends of Travel hours -

                   

                  I have to filter Operational / Activity (Code 17 - As it represents travel Hours)

                   

                  Formula :- travel Hours / total Hours of that particular region * 100

                   

                  For Instance - (Attached Sheet)

                   

                  Aaron - Jan (76.5 / 904 * 100) = 8.5 %

                              Feb (161 / 820,75 * 100) = 19.6 %

                  Juergen - Jan (417.55 / 2143) = 19.4 %

                   

                  And So on..

                   

                  For warranty Trends -

                   

                  Just one thing, In work Type - Like same as Billable, for Warranty - I have to add Product Warranty + Service Warranty and then show into Percentages same as above.

                   

                  The formula I used, same as in the sheet just added Product warranty + Service Warranty (But didn't work)

                   

                  Can you help here !

                   

                  Sorry to bother you, but I will learn this as soon as possible.

                   

                  Thanks !

                  SAKET

                  • 6. Re: Calculations - Using trends
                    Jim Dehner

                    Hi

                     

                    first to the solution

                     

                    I have changed the LOD expression to be a ore general solution that will respond to your filter request - you should be able to drop a dimension filter on the shelf and the combine the selections to get the total you want

                     

                    the formula is         

                              sum(({ INCLUDE  [Work Type]: sum([Hours])}))/sum(({ FIXED [Service Managers],(DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])}))

                    when you use this with the travel code you referenced the viz will become

                     

                    Note that the value that you reference for Feb in you response is different - I believe you crossed the data for Fredric with that for Arron

                    see the table below

                     

                    Note also that the % of hours now sum

                     

                     

                    see the purple tab

                     

                     

                     

                    Now here are some links that cover LOD expressions

                     

                    Overview: Level of Detail Expressions

                     

                    Understanding Level of Detail (LOD) Expressions | Tableau Software

                     

                    Top 15 LOD Expressions | Tableau Software

                     

                    Let me know if this helps

                    Jim

                     

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    1 of 1 people found this helpful
                    • 7. Re: Calculations - Using trends
                      Saket Metkari

                      Jim,

                       

                      This helped a lot, thanks

                       

                      And thanks for the links, I will surely go through it.

                       

                      Thanks !

                      SAKET

                      • 8. Re: Calculations - Using trends
                        Jim Dehner

                        Lod expression take practice.  Don't be discouraged.  You'll get there

                        Jim

                         

                        On Jul 2, 2017 10:48 AM, "Saket Metkari" <tableaucommunity@tableau.com>

                        • 9. Re: Calculations - Using trends
                          Saket Metkari

                          Hi Jim,

                           

                          Sorry to bother you again, but I have another problem regarding different scenario.

                          As you understand my problems very well, need your help.

                           

                          1) Please find attached Excel - Dashboard, I need to create these dashboards

                          The problem is dates (I need to create 2 parameters - Start Date & End Date)

                          When  I select Sep 17 in start date parameter and Aug 18 date in End Date Parameter, it should display Employee Details between these period.

                           

                          2) I need to show trends of Head Counts using Product Lines and Positions on a Monthly Basis.

                          Like Month by Month how the count is increasing.

                           

                          Problems

                           

                          When I create pivot of all date columns the number of rows gets increased and my head count is wrong.

                           

                          For your more understanding I am sharing you an excel file(dashboard which I have to prepare), the source file and tableau workbook.

                           

                          Thanks your for your help and understanding.

                           

                          Need your help here please.

                           

                          Thanks !

                          SAKET

                          • 10. Re: Calculations - Using trends
                            Jim Dehner

                            Hi

                            I cant tell look at your data what you want to to count but I think this is it      Countd([Sso Id])    - where SSo ID is the unique identifier that you want to count - the Countd just say only count each ID once -

                             

                            I made this text chart  - it is under the see this sheet tab

                             

                            Jim

                             

                            • 11. Re: Calculations - Using trends
                              Saket Metkari

                              Hi Jim,

                               

                              When I do this in pivot, then I can get this calculations (Attached Screenshot)

                               

                              Can you please help me with the date problem ?

                               

                              Thanks !

                              SAKET

                              • 12. Re: Calculations - Using trends
                                Jim Dehner

                                Ok here is the issue - your data set brought the date field (now called pivot field names) in as a String field (text)- not a date field

                                 

                                Typically I would just click on the icon next to the name and convert it to a date - but your field is in a day-month-year format (is that a standard in europe?) and that did not work with my default of month-day-year

                                 

                                I did a "Brute Force" approach using the Date function

                                 

                                          date(Mid([Pivot Field Names],4,3)+"-"+left([Pivot Field Names],2)+"-"+"20"+Right([Pivot Field Names],2))

                                 

                                This function just takes the string apart and make it month - day year - and then convert it to a date

                                 

                                it is called new date on the attached and it is what you can use in your viz   - it will work as a date calendar - a better solution would be to try to click on the icon next to the name Pivot Field Name in your left data frame and select Date and see if it converts the way you want (your defaults may be different than mine)

                                 

                                Jim

                                 

                                • 13. Re: Calculations - Using trends
                                  Saket Metkari

                                  Thanks a lot, this helps !

                                   

                                  Now, I have to calculate Count of employess according to positions each month.

                                   

                                  here I have a problem, if you check screenshot it gives me wrong count.

                                   

                                  Thanks !

                                  SAKET

                                  • 14. Re: Calculations - Using trends
                                    Jim Dehner

                                    I believe this is the solution I sent you earlier

                                     

                                    where the count is = Countd([Sso Id])

                                     

                                     

                                    1 2 Previous Next