7 Replies Latest reply on Oct 22, 2019 7:26 AM by Tim Beard

    Please help me calculate Win Rate based on parameter choice

    Alexandre Marson

      Hi everyone,

       

      First, I'd like to thank you all in advance for making time and helping others. This is an awesome resource.

       

      I have been learning Tableau for the last few months but still trying to grasp certain concepts like LOD, which I suspect will be the solution to my issue.

       

      I am trying to calculate opportunity Win Rate and show it in a dashboard based on the time frame selected on a parameter (Year, Qtr, Month, Week). I have tried to make this work for several hours to no avail, despite searching extensively both on this community and elsewhere.

       

      Please find sample workbook attached. My sad attempt at calculating this can be seen in the "Time frame Win Rate" formula. As you can imagine, that was not the only version I tried... one of many .

       

      Thank you!

      Note: Our fiscal year starts in July. I have already set up CloseDate as such.

        • 1. Re: Please help me calculate Win Rate based on parameter choice
          Tim Beard

          Hi Alexandre,

           

          I've downloaded your sheet, but I'm a bit confused as to what you want to have displayed. If "Week" is selected, should it display a single figure for the most recent week or show a graph of the win rates each week as below?

           

          I haven't had a close look at your calculation, but if you're looking for a single figure for the latest week/month/quarter/year, then I'd probably simply use your Oppt. Win Rate calculation (SUM of Won ops over the count of Opportunities) and use the parameter to set a filter to only include the figures for the last time period using some kind of max function.

          • 2. Re: Please help me calculate Win Rate based on parameter choice
            Alexandre Marson

            Hi Tim,

             

            My Dashboard will have "metrics that matter" at the top, being displayed as #s (win rate = xyz%). See attached image. That is from my real Dash and shows how the client wants it.top of dash image.JPG

             

            If I add the "Opp. Win Rate" calc. field alone it will be static. I have this parameter in the Dash so users can choose the time frame and all the data flexes with it. I was able to build calc fields for all other vizzes, in the same method as the "Timeframe Win Rate". The formula below should work, but for reasons I can't grasp it simply isn't.

             

            Do you see anything causing the formula to not properly "flex" the data?

             

            CASE [Select Timeframe]

            WHEN "year" THEN

                IF ATTR(YEAR([Close Date])) = YEAR(TODAY())

                THEN [Opp. Win Rate]

                END

            WHEN "month" THEN

                IF ATTR(YEAR([Close Date]))= YEAR(TODAY()) AND ATTR(MONTH([Close Date])) = MONTH(TODAY())

                THEN [Opp. Win Rate]

                END

            WHEN "quarter" THEN

                IF ATTR(DATEPART('quarter',[Close Date]))=DATEPART('quarter',TODAY())

                THEN [Opp. Win Rate]

                END

            WHEN "week" THEN

                IF ATTR(DATEPART('week',[Close Date]))=DATEPART('week',TODAY())

                THEN [Opp. Win Rate]

                END

            ELSE 0

            END

             

            The idea is to show current year, week, month, quarter data.

             

            Thank you so much.

            • 3. Re: Please help me calculate Win Rate based on parameter choice
              Tim Beard

              Hi Alaxandre,

               

              Thank you for a nice practice for my date calculations as part of my exam prep!

               

              I think the issue was needing to calculate at the aggregate level, when you needed to aggregate row-level calculations. Or something like that. Hence your calculation was generating nulls. It gets a bit confusing, but it just kind of felt wrong when I saw the calculation I searched for "sum of current year" and came across this discussion from 2016, which helped me.

               

              I think I've come up with something along the lines of what you need. I have made the assumption that the calculation you are looking for is always "the result for the latest (current) year/quarter/month/week"

               

              CASE [Selected Timeframe]

              //Current year

              WHEN "year" THEN

                  SUM(IIF (YEAR([CloseDate])= YEAR(TODAY()),[Won Opps.],0))

                  /

                  SUM(IIF (YEAR([CloseDate])= YEAR(TODAY()),1,0))

              //Current quarter

              WHEN "quarter" THEN

                  SUM(IIF(DATETRUNC('quarter',[CloseDate])=DATETRUNC('quarter', TODAY()), [Won Opps.],0))

                  /

                  SUM(IIF(DATETRUNC('quarter',[CloseDate])=DATETRUNC('quarter', TODAY()), 1,0))

              //Current Month

              WHEN "month" THEN

                  SUM(IIF(DATETRUNC('month',[CloseDate])=DATETRUNC('month', TODAY()), [Won Opps.],0))

                  /

                  SUM(IIF(DATETRUNC('month',[CloseDate])=DATETRUNC('month', TODAY()), 1,0))

              //Current week

              WHEN "week" THEN

                  SUM(IIF(DATETRUNC('week',[CloseDate])=DATETRUNC('week', TODAY()), [Won Opps.],0))

                  /

                  SUM(IIF(DATETRUNC('week',[CloseDate])=DATETRUNC('week', TODAY()), 1,0))

              END

               

              What this does is:

              - For each row:

                   - Is this the current period? If so, then give me the figure for Won Opps (= a number or 0)

              - Sum all of the above

              - For each row:

                   - Is this the current period? If so, then give me 1

              - Sum all of the above

              - Divide the first sum by the second (Value of sum of Won Opps for the current period divided by the number of Opportunities for this period)

               

              This generates a figure for all values of the parameter except for Week, which makes sense as there are no data for this week.

               

              I hope this generates the figure you were looking for! Please let me know.

               

              Tim

              p.s. When I'm having problems working out what I need to do, I often end up writing things out on words based on the fields I have available. It's worth having a look at the videos that I linked to in my post here. The video about understanding the data has been invaluable to me. I can't get the word "grain" out of my head now!

              1 of 1 people found this helpful
              • 4. Re: Please help me calculate Win Rate based on parameter choice
                Alexandre Marson

                HI Tim,

                 

                Thank you so much!!!

                 

                It works.

                 

                The last link you provided is taking me to the community welcome page (https://community.tableau.com/welcome). If you don't mind re-posting the link, it would be awesome.

                 

                I tried to break the formula down to smaller "chunks" but I just couldn't wrap my mind around win rate calculation. It is so simple . It makes complete sense to me now but I would love to see that link for the thought process and apply to my future needs.

                 

                Again, I truly appreciate your time and help!

                 

                Alex Marson

                • 5. Re: Please help me calculate Win Rate based on parameter choice
                  Tim Beard

                  Hey Alex,

                   

                  Glad it worked!

                   

                  Sorry about the dodgy link. Here it is in full:  Aggregation problems driving you mad? Looks like the forum software is trying to be too helpful as I can't paste the link as text. Just add /thread/304049 to the end of community/tableau.com

                   

                  I was pondering on the bus this morning about why the original calculation "felt wrong" to me (sad, I know). As ever, I found putting it in words helped me to understand:

                      IF ATTR(YEAR([Close Date])) = YEAR(TODAY())

                      THEN [Opp. Win Rate]

                  The first part says look at all rows and test if this condition is met.

                  If so calculate the win rate (divide one column entry by another)

                   

                  If there are 100 rows with this condition, then tableau should try to calculate this 100 times? What figure should be produced at the end? Hence the need for aggregation. Somewhere

                   

                  One also needs to think about whether the calculation should be done on aggregated fields or if calculation results should be aggregated. Another mental challenge. For this point, also have a look at one of the free training videos, Aggregation, Granularity, and Ratio Calculations , especially from around 2:19 which discusses calculating profit ratios as Profit/Sales and SUM(Profit)/SUM(Sales). I always find myself coming back to these videos. Very helpful.

                   

                  Good luck with the rest of the project!

                   

                  Tim

                  1 of 1 people found this helpful
                  • 6. Re: Please help me calculate Win Rate based on parameter choice
                    Alexandre Marson

                    Very helpful Tim,

                     

                    I was given the opportunity to work on creating a global dashboard for my employer and I welcomed it... until I felt overwhelmed .

                     

                    I feel like I am turning a corner though and starting to understand how tableau works, I am getting there. Not a lot of time to "study" Tableau so I am learning as I go and threads like this one have helped me a lot so huge thanks to all of you who contribute to this resource.

                     

                    Thanks Tim Beard

                     

                    Alex

                    • 7. Re: Please help me calculate Win Rate based on parameter choice
                      Tim Beard

                      Cool - glad it helped!

                       

                      Once you understand the data and what impact it has on your calculations, you've made a big step forwards. I was getting myself into a bit of a mess before. Hence my post about it once I'd figured it out.

                       

                      Internalising the impact of how discrete and continuous data is handled also helps.

                       

                      I'd suggest taking table calculations very slowly.

                       

                      And leave off data densification for a while . That stuff will really do your head in.

                       

                      Good luck on your journey. It's also worth looking at these forums if you have time. I've also learned a lot by trying to sort out other people's issues. This one included!

                      Cheers,

                      Tim

                      1 of 1 people found this helpful