
1. Re: Please help me calculate Win Rate based on parameter choice
Tim Beard Oct 18, 2019 9:39 AM (in response to Alexandre Marson)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 Oct 18, 2019 3:00 PM (in response to Tim Beard)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.
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 Oct 21, 2019 2:24 AM (in response to Alexandre Marson)1 of 1 people found this helpfulHi 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 rowlevel 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!

4. Re: Please help me calculate Win Rate based on parameter choice
Alexandre Marson Oct 21, 2019 12:20 PM (in response to Tim Beard)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 reposting 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 Oct 22, 2019 1:49 AM (in response to Alexandre Marson)1 of 1 people found this helpfulHey 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

6. Re: Please help me calculate Win Rate based on parameter choice
Alexandre Marson Oct 22, 2019 7:16 AM (in response to Tim Beard)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 Oct 22, 2019 7:26 AM (in response to Alexandre Marson)1 of 1 people found this helpfulCool  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