How to parse out highest Orange level employee and filter all relevant data accordingly?

Question & Answer
, , ,
1

I am looking to see how I can use a series of calculated fields to parse out the highest rank employee (by the color orange). But at the same time I want to show the location name and a headcount measure of all employees distinguished by color for each department? I’ll leave a sample dataset below that contains dummy data, as well as a visual of the outcome I’m looking to achieve in my table. Much help from anyone who has a solution to this!

Sample Data Request
Sample Data Request1806×430 34.8 KB

2

Hey @manbig. Hopefully you will hear some suggestions from the Community soon. Just in case, I have pinged one of our SAs to see if they can reply back on Monday! Hope you are having a good weekend! :slight_smile:

3

Thank you for the update @Kristin hope to hear back soon on this topic!

1 Like
4

Hi @manbig, sorry for taking a couple of days to get a solution to you. Here’s what I did:

  • Create a calculated field for Orange ifelse(Color = 'Orange', 1, 0), and one for Blue ifelse(Color = 'Blue', 1, 0). You should try to do these in data prep if possible.

  • Create a calculated field called IsOrange ifelse(Color = 'Orange', 1, 0) (you need this to be used in the grouping, but you need your ‘Orange’ field to be a measure, so you do need two fields with the same calculation).

  • Create a calculated field for Total Orange + Blue

  • Create a calculated field called Display Rank maxOver(ifelse(Color = 'Orange', {Employee Rank}, 0), [Department], PRE_FILTER). This sets non-Orange rank values to 0 and then gets the max, which will give you the highest Orange value

  • Create a calculated field called Display Name lastValue(Name, [IsOrange ASC, {Employee Rank} ASC], [Department])

  • Create a pivot table and add the following in this order to Rows: Department, Display Rank, Location, Employee Rank, IsOrange, Name. Set the last 3 of these to hidden. If you don’t include them you’ll get an error, but they can be hidden.

  • Add Display Name, Orange, Blue, Total.

  • Open the Format Visual pane for the pivot table, select Hide +/- buttons in Pivot Options.

  • In Total, set Show totals, and change the Label to Grand Total. You can change the background if you want to.

  • In Subtotal, set Show subtotals, change Level to Custom and select Department from the dropdown. Add background styling if you need.

  • In Row names, change Display Rank to Employee Rank, and in Value names change Display Name to Name.

image
image693×469 32.3 KB

Hope that helps!

2 Likes
5

Thank you @Steph! :slight_smile:

@manbig, if Steph’s solution works for you, we’d love it if you could please help out the community by marking it as solved (check box under her reply). :slight_smile: Thanks!

1 Like
6

Hello @Steph and @Kristin thank you for getting back to me! I was working through the solution you’ve provided and was running into an error on Step 4. I’ll attach a screenshot of what the error message relays. Could I get some help on trying to fix this!

Error Message
Error Message810×150 30 KB

7

Hi @manbig, looks like you’re trying to set the Display rank to a string, and that your Employee Rank field is also as string. The maxOver field expects a measure or date, and so you would need that field to be an Integer. If you can’t change the Employee Rank field in the Data Preparation area (which I would recommend if you can), you can use parseInt in this function
maxOver(ifelse(Color = 'Orange', parseInt({Employee Rank String}), 0), [Department], PRE_FILTER)

2 Likes
8

Thanks for the clarification @Steph! Since I don’t have access to edit the original data source, adding the ‘parseInt’ function and transforming it to a measure field worked perfectly! You Rock :grin:

2 Likes