Hi Please try the following calculation by creating c calculated field.
Also, fill the blank cell with "No" before you created the calculation or you might need to use "" instead of "No"
WHEN "Yes" THEN (CASE Group WHEN "Overlap" THEN "Peer & Overlap"
ELSE "Peer Company" END)
WHEN "No" THEN (CASE Group WHEN "Overlap" THEN "Overlap Company" END)
After trying that, when I select "Overlap Company" in my filter from that calculation, only A-E are displayed. Self, N, O, P, Q, R should also appear as Overlap.
I am a little bit confused. For the three groups, "Peer", "Overlap" and "Peer & Overlap", do you want them exclude from each other and then the sum equals the total number of records?
If so, when you select "Overlap Company", only overlap (No Peer) companies should show, which is A-E. N,O,P,Q,R are categorized as "Peer & Overlap" in my calculation.
Please let me know if I misunderstand what you want.
Sorry for any confusion. It is a bit complicated. Basically I want the final filter to be Peer, Overlap, or all.
Peer would contain: F-R
Overlap would contain: A-E; Self; N-R
1 of 1 people found this helpful
You're able to do this using a parameter. Create a parameter displaying the values Overlap and Peer.
Create a calculated field to reference that parameter AND the 'Group' field you have in your dataset.
The calculated field formula:
IF [Parameter] = 'Peer' AND CONTAINS([Group],'Peer') THEN 'Peer'
ELSEIF [Parameter] = 'Overlap' AND CONTAINS([Group],'Overlap') THEN 'Overlap'
Drag the calculated field to the filters shelf and set it up to Exclude NULL values.
That should give you what you're asking for.
Thanks Andrew, I am still having trouble. I want to check that I created the parameter correctly: I right-clicked on Group under my dimensions to create the parameter. "Peer & Overlap" originally appeared there in the list, but I removed it.
Then I did the calculated field exactly as you wrote it, except with the name of my parameter. When I brought the calculated field on to the Filters shelf, I was expecting to see Overlap and Peer, but instead, this is what I see.
Any idea where I might be going wrong?
It looks correct. You're not seeing Peer in the filer list because you have Overlap selected in the parameter (even though you may not know this as it's not displayed). If you change the parameter and select Peer then Overlap wil no longer appear in the filter list - which is why I've suggested you exclude the NULL values.
Ok, I think I follow you. So this method allows me to do the filtering behind the scenes. However, I would like the people who are going to view my finished product to be able to select between the two groups with a standard filter that appears on the dashboard.
Thanks, think I've almost got it. In my Parameter (that appears as a filter), now I definitely see the choice between Peer and Overlap - last question, how can I get "All" to appear or be able to select both groups?
Excellent - I got it! Thanks for your patience.
Here is the final calculation in case others need it:
IF [Group Parameter] = 'Peer' AND CONTAINS([Group],'Peer') THEN 'Peer'
ELSEIF [Group Parameter] = 'All' THEN 'All'
ELSEIF [Group Parameter] = 'Overlap' AND CONTAINS([Group],'Overlap') THEN 'Overlap'