2 Replies Latest reply on Jul 15, 2013 6:38 AM by Kris Erickson

    Need some calculation help

    Brooke Bondur

      Hi Everyone,

       

      I posted this in the Forum a week ago and haven't had a response.  I'm hoping someone in ATUG can help me figure this out.  I've exhausted every solution I've been able to find in the Forums.  Here is the case:

       

      I am trying to determine the Average Cycle Time for two different calculated periods.

       

      The first period is the difference between [Date_Quoted_To_Customer] and [SOIDATE].  The second period is the difference between [OppHeaderCreatedDate] and [RSDAPPROVALDATE].

       

      The first period is called [Bid to Close Days] and I used this formula to calculate it: DATEDIFF('day', MAX([Date_Quoted_To_Customer]),MAX([SOIDATE]))

       

      The second period is called [Deal Qual Days] and I used this formula to calculate it: DATEDIFF('day',MAX([OppHeaderCreatedDate]), MAX([RSDAPPROVALDATE]))

       

      I want to show the current Average value of each of these calculated periods based on some display and filter criteria.  I created separate WINDOW_AVG calculations for each of the periods and the result does not match my check that I did separately in Excel.

       

      I should clarify the AVERAGE calculation I'm looking for:  I have created a filter called BID STAGE.  I want to calculate the Average Bid to Close Days and Average Deal Qual Days for the records in each BID STAGE, by SOIDATE year.  That is where I'm stuck.  Every solution I have tried doesn't match what I calculate separately using the AVERAGE calculation in Excel.

       

      I'm sure that is has something to do with the fact that my cycle time calculations are aggregations but I'm at a loss on how to work around it.  My ultimate goal is to create a KPI dashboard for some Sales metrics and these two Averages will be part of the criteria for some of them.

       

      Thanks in advance for the help!

       

      Brooke Bondur

        • 1. Re: Need some calculation help
          Kris Erickson

          I looked at it, and I think you need to do the heavier lifting outside of Tableau.  I could not get the summary tab to behave.

           

          HOWEVER!  I was able to get the check table to work.  First of all, I tend to use the first() formula on and table agg as a guide for such things.  I know what when it resets for each bid stage then I have the right "configuration" I need for average (or max or min) for that grouping.  I then applied the same settings to your window average and got the correct average answer for that group:

           

          Here are the settings for those table calcs.  Edit table calculation and go to advanced:

           

          That will give you the right Excel calculations.

           

          But I'm stumped how to bring that into the summary sheet. I would love to clarification from some other members.

          • 2. Re: Need some calculation help
            Kris Erickson

            Actually take a look at the awesome workbook located here:

            http://drawingwithnumbers.artisart.org/the-next-n-table-calculation/

             

            I think #6 might be what you are interested in.