14 Replies Latest reply on Mar 6, 2018 8:00 AM by Joe Oppelt

    data blending multiplication

    Jane Wu

      Hi

       

      I am trying to do multiplication by data blending. Same workbook but different tabs trying to get field from secondary source and do multiplication to get actual work hours.

       

      1) and 2) are join by employee ID

       

      Tabs

      1)TimeSheet:gives numbers of hours employee is using to do each task

      2)Sheet1: [FTE] gives the percentage of time employee is working

      3)Networkdays: gives the number of work hrs per month

       

      1) and 3) join by edit relationship

      Edit relationship:

       

      [TimeSheet].[current mth] =[Networkingdays].[month]

       

      StartEndMonthNetworkdaysWork Hours
      01-Jan-1831-Jan-18123172.5
      01-Feb-1828-Feb-18220150
      01-Mar-1831-Mar-18322165
      01-Apr-1830-Apr-18421157.5
      01-May-1831-May-18523172.5
      01-Jun-1830-Jun-18621157.5

       

      Desire results: give FTE=0.7

      When it is Jan 0.7 x 172.5

      When it is Feb 0.7 x 150

      When it is Mar 0.7 x 165

       

      First Method:

      Every time I created calculated fields I get error message (cannot mix aggregate with non aggregate), [Work Hours] becomes automatically sum.

       

      [FTE]*SUM([Networkdays (Test blend data calculation)].[Work Hours])

       

      So is it impossible to multiple fields from separate data sources??

       

      Method 2:

      Actual Hrs Logics: (calculated Field)

       

      IF [Current Mth]=1 THEN [FTE]*172.5

      ELSEIF  [Current Mth] =2 THEN [FTE]*150

      ELSEIF [Current Mth]=3 THEN [FTE]*165

      ELSEIF [Current Mth]=4 THEN [FTE]*157.5

      ELSE END

       

      syntax error, can you please tell me where is the mistake?

        • 1. Re: data blending multiplication
          Joe Oppelt

          All data from a secondary source comes back as an aggregate -- even if there is only one value from the secondary source.


          So yes, in [Actual Hrs] you can only use the value from the secondary as an aggregate, and that's why you see:

           

          SUM([Networkdays (Test blend data calculation)].[Work Hours])

           

          So you have to make FTE look like an aggregate too.  A common way is to wrap a dimension in ATTR().  I did that for you in the attached.

           

          I also fixed [Actual Hrs Logic] for you.

           

          See attached.

          • 2. Re: data blending multiplication
            Jane Wu

            Hi Joe,

             

            I have to remember this ATTR trick for secondary data. Actual Hrs are the correct answers I am looking for. Thank you for your help!  Can you please tell me why Actual Hrs logic is the same formula as Actual hrs but the answer is not the same?

             

            [FTE]*172.5 = 120.75 but Actual Hrs logic is 18,837.

             

            Thanks,

            Jane

            • 3. Re: data blending multiplication
              Joe Oppelt

              [Actual Hrs] is being evaluated at the aggregate level.  We have one value for [Work Hours] because it comes back as an aggregate from the secondary source at the month level.  So one value for each month.  And wrapping [FTE] in the ATTR() function tells tableau to draw a single number for FTE at the month level as well (since that's the level of granularity we're working at on the sheet.)  So the result is a single number.

               

              [Actual Hrs Logic] is working at the row level in the table.  Every row has a value of [FTE]*172.5.  I didn't really look at what it was doing.  I just corrected the syntax and dragged it onto the Measure Values shelf.  Tableau defaults to doing a measure as a SUM, so we're getting the SUM of the results of [FTE]*172.5 for all the rows that are getting aggregated into month=1.  For demonstration purposes, I dragged [Number of records] onto Measures shelf, and there are 156 rows in Month=1.  So were getting 156 times [FTE]*172.5.  In the attached I changed the pill on the measures shelf to AVG() instead of SUM().  Probably MIN() or MAX() would have resulted in the same thing.  Don't add them all up.  Just get one value.

               

              This actually demonstrates why we needed to do ATTR() in [Actual Hrs].  The [Work Hours] value was a single, aggregated value, but simply using [FTE] in that calc meant that Tableau was seeing 156 occurrences of [FTE].  True, in your case all 156 values are the same, but that's not always the case.  So we tell tableau to handle [FTE] the same way that [Work Hours] is being handled -- do everything at the aggregate level.  When one thing is aggregated, everything has to be aggregated.

               

              And to demonstrate this further, look at the copy of [Actual Hrs Logic (copy)] I created.  In there I wrapped everything in ATTR(), which makes the calc an aggregate calc instead of a row-level calc.  When I drag that onto Measures shelf, it goes on as ATTR(actual hrs logic (copy)) rather than needing to have some SUM() or AVG() in front of it like the original calc does.  BEcause everything in the copy is done at the aggregate level, Tableau does the work at the level of detail that your sheet dictates (at the month level in this case.)

              • 4. Re: data blending multiplication
                Jane Wu

                Hi Joe,

                 

                Thank you for taking the time to explain detail calculation to me. Still new to tableau on my second month. I agree I like the calculation of Actual Hrs. However, all three different methods are aggregate calculations now for my next step. I calculated the Billable Utilization using Actual Hrs, Actual Hrs logic and Actual Hrs Logic (copy).

                 

                What should I do on my sheet 2 when I want to show AVG Billable Utilization? I am totally stuck right now. I can't think how to move forward? is there a way?

                 

                Thank you so much for your help.

                Jane

                • 5. Re: data blending multiplication
                  Joe Oppelt

                  Tell me a little more about what you are averaging.  On the first sheet you have these averages broken out by name.

                   

                  On the first sheet for Billable 1 in Month 1, you have 0.8 and 0.5 for the two names.  Do you want to arrive at 0.65 for the average billable 1 on sheet 2, for example?  (And likewise for all the months and all the Billable categories...)

                   

                  Just as we discussed two different levels of calcs earlier (row-level vs aggregate-level), there is a 3rd level called table calcs.  Depending on the answer you give to my question above, we likely will need to use table calcs, which are calcs of aggregates.  And I can show you how to do that.

                  • 6. Re: data blending multiplication
                    Jane Wu

                    Hi Joe,

                     

                    Yes, I would like to arrive at answer of 0.65 for average for month 1 average for one level and later on second level for all months within billable utilization 1. I assume billable utilization 2 and 3 will be the same methodology. Since all three are aggregate calculations. 

                    Thanks,

                    Jane

                    • 7. Re: data blending multiplication
                      Joe Oppelt

                      See attached.  Sheet (2) is a copy of your original.  I took off all the measures except the three we're concerned with.

                       

                      I created a calc that does WINDOW_AVG of Billable 1.  WINDOW_AVG is a table calc.  It acts on aggregates.  (Billable 1 is an aggregate.)  You can see on Sheet (2) that the number is not correct, of course.  The value is the average of all the values of Billable 1 on the sheet.

                       

                      Go to Sheet (3).  I edited the table calc to tell it how to "walk" through the table.  See screen shot:

                       

                       

                      To get this dialog box, right click on the table calc n the measure values shelf and select "edit table calc".

                       

                      Once it's set this way, you can see the results.

                       

                      Now go to sheet 4.  Here I moved the Name and FTE pills to the Details shelf.  Now we have only 4 rows, but 2 values in each cell (because there are 2 values for each Name/FTE.  We're getting closer.

                       

                      Go to Sheet 5.  I added a new calc called [index],  INDEX() is a function that tells us what position in the table Tableau considers each mark.  I set the table calc settings to match those I have on the average calc.  Now we get a count of 1-through-N for as many names as will be on the table.

                       

                      Go to Sheet 6.  I dragged [index] from the Measures shelf to filters.  Select for value = 1, and now we have only one mark per cell.  The average calc is giving you the correct number for each month.

                       

                      See if you can duplicate the same for the other 2 measures.

                       

                      Once you have them on the sheet, you can drag the three original measures off and just leave the three table calcs.  That's the sheet you were looking to see.


                      Once we get to that point, then we can talk about summing up the averages.

                      • 8. Re: data blending multiplication
                        Jane Wu

                        Hi Joe,

                         

                        I used it on my actual data which is a lot bigger, couldn't get it working. I expended my dummy data set, still can't get it to work. I think I am just missing something on my table cal setting or something is wrong with my index.

                         

                        In the future user of my template would like to filter the views by diff business unit, mgmt. dept, job function and region. So I added those in the filter for now to see what they look like.

                         

                        Did you create the index as discrete or continuous? Also for table calculation, I understand specific dimension, I understand at deepest level, however, "restarting every" how do you determine which metric to use?

                         

                         

                        Thanks for walking me through this.

                        Jane

                        • 9. Re: data blending multiplication
                          Joe Oppelt

                          Jane -- Help me understand what I'm looking at in the new workbook.  I recognize the "Billable Hrs" sheet, but where did you try what I did?  (And what measure are you trying to aggregate, and what value are you expecting to get?)

                          • 10. Re: data blending multiplication
                            Jane Wu

                            Hi Joe,

                             

                            I really confused myself after I add in extra columns I went back to your file and follow step by step. I was able to get to close to your methodology in tab (Successful Ave Billable Utilization. However rest of the utilization formulas I thought I used the same methodology but I can't come correct number for average like I did for Billable utilization.

                            • 11. Re: data blending multiplication
                              Joe Oppelt

                              Jane Wu wrote:

                               

                              ...

                               

                              Did you create the index as discrete or continuous?

                              Let me first answer one of the easier questions...

                               

                              It doesn't matter about that.  If your pill is CONTINUOUS, then the filter editing looks like this:

                               

                              And you set both the min and max of the range to 1.

                               

                              If it is DISCRETE then it looks like this:

                               

                               

                              and you just check the box for 1.  The filter works the same either way.

                              • 12. Re: data blending multiplication
                                Joe Oppelt

                                In your latest workbook you have the table calc set correctly for INDEX on the filter shelf:

                                 

                                 

                                For [AVG Billable Utilization] you do not have it the same way:

                                 

                                 

                                I changed it to this:

                                 

                                 

                                To get the Current Month to the top of the list (which is important because we are restarting every month) you can just drag it to the top.


                                Note:  You'll notice that Region and Name are not in the same order for this calc as for INDEX.  Sometimes it would matter, but in this case you have only one value in each dimension on the table, so the 1-for-1 relationship doesn't change anything when the tables is walked in a slightly different order here.

                                 

                                I started looking at some of the other table calcs you have on the sheet and the ones I looked at were set correctly.  I didn't check them all.

                                 

                                Maybe [AVG Billable Utilization] was just an oversight.

                                 

                                You asked a question about "restart every".  We restart every month (in this case) because you want the averages calculated for each month.  (Or, at least, that's my understanding of your requirements.)

                                 

                                A really helpful study of how tableau walks a table is to display the value of [index] and change settings to see what it does.  I made a copy of your "Successful Ave...' sheet, took out most of the measures, and put [index] on TEXT so you can see the values.  Edit the table calc settings for [index] on that sheet.  Change it to "restart every" (none).  You'll see that index changes to 1-through-N down the sheet, for example.  When I am trying to tame a complicated table to process in a certain order, I first put [index] on my sheet like this.  Then I get the index numbers to behave the way I want it.  Once I get the [index] sequencing correct, then I know to set any other table calcs with the same setting.

                                • 13. Re: data blending multiplication
                                  Jane Wu

                                  Hi Joe,

                                   

                                  Thank you for your detail explanations. Felt like this table cal is like Level of Detail calculations. If all the field are in the original data set already would be like this.

                                  {FIXED [Business Unit],[Region],[Current Month]:AVG([Billable Utilization])}

                                   

                                  Is it because we calculated Billable Utilization with aggregate functions, we have to use Table Calculation.

                                  1) we use ATTR to look like a aggregate function to calculation Actual Hrs (Secondary source data -Work hours pulled in as aggregate calculation). ATTR([FTE}*SUM(Networkdaying.Work hours)

                                   

                                  2)we calculate Window_AVG is used to calculate average for aggregated measure Window_AVG([Billable Utilization])

                                  would it be possible to use AVG(ATTR([Biliable Hours)/[Actual Hrs])?

                                   

                                  Instead of writing two calculated fields below:

                                   

                                  #1 ATTR(Billable Hours)/[Actual Hrs] =Billable Utilization

                                  #2 Window_AVG(Billable Utilization) = AVG Billable Utilization

                                   

                                  AVG is only possible for data set calculation, cannot use on aggregate calculations?

                                   

                                  3)Index() is for checking data, check how many marks on the cell, set to index() =1, drag to Text and Filter

                                   

                                  I have one last question. In the third tab (Successful Average Billable Utilization), how do I come up with a YTD Average, or when I have one year data, how do I come up with average utilization for whole year?

                                   

                                  I tried pull Current Month to detail, leave only Year(Work Date) in Rows I see four numbers of average still. Would I do Avg of all (Window_Avg(Billable Utilization))?

                                  ie 0.3638+0.0714+0.0560+0.0666)/4 =.13945

                                   

                                  Thanks, Jane

                                  • 14. Re: data blending multiplication
                                    Joe Oppelt

                                    Jane Wu wrote:

                                     

                                    ...If all the field are in the original data set already would be like this.

                                    {FIXED [Business Unit],[Region],[Current Month]:AVG([Billable Utilization])}

                                     

                                    Is it because we calculated Billable Utilization with aggregate functions, we have to use Table Calculation.

                                     

                                     

                                     

                                    That's precisely why.  Once the calcs have aggregates in them, LODs are off the table.  The start of aggregates in the string of calcs began with [Actual Hrs].  Data coming from a secondary source must come as an aggregate.  And once an aggregate is in the picture, all fields must be aggregates, and that's why we had to do ATTR() in [Billable Utilization].


                                    And then, the only way to further sum up calcs that aggregates is to use table calcs.

                                     

                                    Jane Wu wrote:

                                     

                                    ...

                                     

                                    2)we calculate Window_AVG is used to calculate average for aggregated measure Window_AVG([Billable Utilization])

                                    would it be possible to use AVG(ATTR([Biliable Hours)/[Actual Hrs]) ?

                                     

                                    ...

                                     

                                    AVG is only possible for data set calculation, cannot use on aggregate calculations?

                                     

                                     

                                    Your underlined statement is exactly right.  If you try to do an aggregate function on something that is already an aggregate, you'll get an error telling you that.  So the next level of aggregation is a table calc, which is why we did WINDOW_AVG().

                                     

                                    Jane Wu wrote:

                                     

                                    ..

                                     

                                    3)Index() is for checking data, check how many marks on the cell, set to index() =1, drag to Text and Filter

                                     

                                    ...

                                    INDEX is useful for that, but there are other times I use it.  (  IF INDEX()=1 THEN do something ELSE do something else END )  This lets me the first value in a string of values -- usually when I'm doing a table calc to process a string of values.  (For instance to set the color of the first value.)

                                     

                                     

                                     

                                    Jane Wu wrote:

                                     

                                    ...

                                     

                                    I have one last question. In the third tab (Successful Average Billable Utilization), how do I come up with a YTD Average, or when I have one year data, how do I come up with average utilization for whole year?

                                     

                                    ...

                                    In the attached, on "Successful Avg Utilization (2)", I added a new calc:  [YTD SUM].  Unlike our limitation that we cannot further aggregate an aggregate measure, we can nest table calcs as deeply as we want.  [YTD SUM] is a WINDOW_SUM of our [AVG...] table calc.  I added it to the text shelf.

                                     

                                    Some points to notice here:  We need the [AVG] calc to process through the table as we had previously defined, but here I want this new calc to add the  values down the table.  So I have this calc set to TABLE(down).  But to make sure that the nested calc is following the proper instructions, I can set the tqable calc settings for the [AVG] calc here too.  See screen shot:

                                     

                                     

                                    Click the pulldown, select the nested calc, and verify its settings.  As it turns out, since that calc was already on the sheet, tableau adopted the existing settings and I'm good to go.  But there are times when you will have to set the settings for the nested calcs here.  And as you can see, it's perfectly OK for nested calcs to have setting different from the top-level calc.

                                     

                                    But there is another problem here.  The sum is coming up as 1.684, but you really need a sum of .5612.  That's because all the rows in the table are getting added together, and we have a value of .3672 for all the rows in Month 1, and .0714 for all the rows in month 2, etc.  Table calc values live on all the rows in the table.

                                     

                                    Go to sheet "... (3)".  Here I made a copy of the [AVG Billable Utilization] calc.  Take a look.  I used INDEX() to set only the FIRST row of each month.  And you can see the results on the sheet.  And then I made a copy of [YTD SUM] to do WINDOW_SUM of that new copy.  Now we get the proper value.


                                    You would do the same sort of thing with the [YTD AVG] calc.

                                     

                                    One further thing to consider about doing table calcs of table calcs.  In your case you have a 1:1 correspondence with the number of rows to the marks on the sheet.  The sum of averages and the average of averages will work successfully here.  But suppose you had 10 rows that together averaged out to 1.0 in month=1, and in months 2 and 3 had one row each that averaged to 0.01.  If you just sum up those 3  averages, you would get 1.02, and if you divided by 3 you would get 0.34.  But if you added up TEN time 1.0 and added to two rows of .01, you would get 10.02, and if you divided by 12 you would get 0.835, and usually that would be the proper weighted average.  to make that happen you would have a calc like this:

                                     

                                    WINDOW_SUM(SUM(Billable Utilization)) / WINDOW_SUM([Actual Hours])

                                     

                                    (Or something like that.  You have to play with it to make sure you're getting the right value.)

                                     

                                    Yup.  It can get complicated.  But I just wanted to raise that awareness for you, and give you direction on how to address it.