10 Replies Latest reply on Aug 31, 2016 12:02 PM by Andrew Watson

    Need help with calculated field - Only show if user performance is above xx%

    mike.shapiro.0

      I'm doing a little data exploration and trying to determine a what if scenario on what would happen if we only looked at a companies performance if they scored above a certain percentage. What I want to do is display a line chart for total performance and another line chart based on only companies that scored above 70% on all of their work over the past 12 months.

       

      Fields that hold these parameters are:

      • Company Name
      • MT Calc
      • Date
      • Order ID

       

      What would allow me to display MT calc only for companies that had an MT average MT Calc of 70% from today to 365 days ago AND if their Order ID count was >20?

       

      Much appreciate any help and happy Friday!

        • 1. Re: Need help with calculated field - Only show if user performance is above xx%
          David Li

          Hi Mike, you can probably do this with some LOD calculations and context filters. First, let me ask, is there a record for each company, date, and order ID? And if so, at how is the MT Calc stored? I.e. if every order ID has its own record, does it also have a unique MT Calc value, or is the MT Calc value the same among multiple records?

          • 2. Re: Need help with calculated field - Only show if user performance is above xx%
            mike.shapiro.0

            Hi David,

             

            Each record has a unique order ID number.  Within every order there is a date, company name and the parameters required to develop an MT Calculation.

             

            MT Calc is a little more involved and is pulled together using several calculated fields.  The current MT Calc that doesn't filter down to only companies that had average MT works like this; it calculates a performance % in one calculated field, then another calculated field says if the performance calculated field = X, then 1, else 0.  Then another calc field takes total performance field and / by total records to get a percentage of MT.  Hope that made sense....

             

            I can duplicate this process, but first I need a way to say only look at companies that had average MT calc of 70% over past 365 days, then do sum of MT Calc / Sum of Total Records

            • 3. Re: Need help with calculated field - Only show if user performance is above xx%
              Joe Oppelt

              I'd help, but I'm having trouble juggling all the details without a sample workbook to play with.

              • 4. Re: Need help with calculated field - Only show if user performance is above xx%
                David Li

                I agree, Joe, it's tough to figure this out without a packaged workbook. Can you post one for us, Mike?

                 

                In case you can't and just want a nudge in the direction of a potential solution, if you put your company into the filter shelf and go to the "Condition" tab, are you able to select MT Calc in the field selector (like Sales is in the example below)?

                 

                • 5. Re: Need help with calculated field - Only show if user performance is above xx%
                  Andrew Watson

                  {FIXED [Company Name]: AVG(IF [Date]>DATEADD('year',-1,TODAY()) THEN [MT Calc] END)}

                   

                  This will give you the average MT Calc for the past year for the companies

                   

                  {FIXED [Company Name]: COUNT(IF [Date]>DATEADD('year',-1,TODAY()) THEN [Order Id] END)}

                   

                  This will give you the order count for companies over the past year

                   

                  You should be able to combine those to something like:

                   

                  IF SUM({FIXED [Company Name]: AVG(IF [Date]>DATEADD('year',-1,TODAY()) THEN [MT Calc] END)}) > 0.7 and SUM({FIXED [Company Name]: COUNT(IF [Date]>DATEADD('year',-1,TODAY()) THEN [Order Id] END)}) > 20 THEN 'include' ELSE 'exclude' END

                   

                  Drag that to the filters shelf, set it to include and if you're lucky it'll work. If not then packaged workbook is required to troubleshoot :-)

                   

                  There could be some syntax errors included in that formula as well, I've typed it directly into the forum and haven't put it into Tableau.

                   

                  The DATEADD component may not be required - you could just filter for the past year and put the filter into context. This formula could (and should) also be broken into separate smaller calculated fields for easier readability and trouble shooting.

                  • 6. Re: Need help with calculated field - Only show if user performance is above xx%
                    mike.shapiro.0

                    Thanks David,

                     

                    I can't provide a workbook unfortunately without reduplicating one as an example.   I may need to search the easiest way to create sample workbooks in the future since I know it makes solving these issues that much easier...

                     

                    I'm attempting the solution Andrew provided and will report back.  Maybe I can isolate the question more by working through the logic a bit further.  Appreciate the help.

                    • 7. Re: Need help with calculated field - Only show if user performance is above xx%
                      mike.shapiro.0

                      I think this logic might work.  I have it all set up, but its displaying only 'null'.  I think that is because its looking at company name to determine dates, but it should look at order ID to determine date and company names...

                       

                      How would I go about having it base everything off the Order ID date, then develop the company name MT calc?

                      • 8. Re: Need help with calculated field - Only show if user performance is above xx%
                        Andrew Watson

                        DId you break out the calculation into smaller calculations to begin? If so are you seeing expected results? For testing I suggest creating a simple table with the Company Name in the Rows shelf. Then you'll be able to see the impact each component of the calculation.

                        • 9. Re: Need help with calculated field - Only show if user performance is above xx%
                          mike.shapiro.0

                          Andrew Watson wrote:

                           

                          DId you break out the calculation into smaller calculations to begin? If so are you seeing expected results? For testing I suggest creating a simple table with the Company Name in the Rows shelf. Then you'll be able to see the impact each component of the calculation.

                          I broke it down into smaller measures, but I think I need to better explain how [MT Calc} works in order to produce a new measure or filter that will calculate company avg MT Calc over a period of time...

                           

                          MT Calc aggregates all cases to provide performance of a general population of data as a % of cases that met the requirement.

                           

                          MT_Calc =  SUM([MT New] )/SUM([new_mt_pop])

                           

                          MT New = If statement that filters case count down based on required results per case.  If the case meets requirements then '1' else '0'

                          New_mt_pop = Develops total population of eligible cases to be considered in MT New

                           

                          At this point I have everything needed to develop the MT calculation for total population.

                           

                          We want to apply a filter that say only show me MT Calc in a time series for cases if the company that did the case has an avg Mt Calc of 0.7 or higher over the past 365 days.

                           

                          From there..... We can explore what would happen if we had only utilized companies that meet this new measure over a period of time.  Maybe this is something that can be accomplished by adding a filter with custom condition formula?

                           

                          Does this help at all?  If not, I'll do my best to create an example worksheet with fictitious numbers/names.  Thanks!

                          • 10. Re: Need help with calculated field - Only show if user performance is above xx%
                            Andrew Watson

                            OK, things are getting a little clearer and a litle more complex. You need to do the 'fixing' of the numerator and demoninator of the MT Calc. Try this:

                             

                            MT NEW: {FIXED [Company Name]: SUM(IF [Date]>DATEADD('year',-1,TODAY()) THEN [MT New If Statement] END)}

                             

                            NEW MT POP: {FIXED [Company Name]: SUM(IF [Date]>DATEADD('year',-1,TODAY()) THEN [Formula to develop cases] END)}

                             

                            For this to work you need to do the fixing of the base calculations, the upper not so important as the results are already 'fixed'.