Discover Which Customers Need More Licenses

In this exercise, you will…

  • Group by Account.Name
  • Group rows by unique users
  • Sort in Descending Order
  • Group by Licensed Seats
  • Filter by Per User license
  • Switch to Compare Tables
  • Format the lens
  • Sort License usage descending
  • Save the lens

Group by Account.Name

  1. Click the + to add a group to the Bars section
  2. Select the LMA.Account__c.Name

Group rows by unique users

  1. Select “Count of Rows”
  2. Select “Unique”
  3. Search and Select User_id_token

Sort in Descending Order

  1. Click the down arrow beside “Unique of user_id_token”
  2. Select “Sort Descending”

Group Columns By Licensed Seats

  1. In the “bars” section, click the “+”
  2. Search and Select LMA.Licensed_Seats__c

Filter by Per User license

You’ll notice we have 2 types of licenses: Site-Wide and Per User.

We’ll start by filtering out the Site License rows so we can focus on the Per User license data.

Filter by licensed seats:

  1. Click “Filter” on the left
  2. Click +
  3. Search and Select LMA.Licensed_Seats__c

Filter out Site License:

  1. Then “Does Not Equal”
  2. Check off Site License (at the bottom of the list)
  3. Click “Add”

Switch to Compare Tables

Now we want to switch to compare tables which will allow us to create additional calculations within the dataset.

Change the Table Mode:

  1. Select Table Mode in the upper right
  2. Select “Compare Table”

Create Formula for Licensed Seats:

  1. Go back to “DATA” on the left-hand side
  2. Click the “+” in the columns section
  3. Click “Add Formula” (top right of that window)

This will bring you into the Compare Table edit mode with a new column

  1. Enter “License Seats” in Column Header
  2. In Functions, select the string_to_number formula
  3. In Fields, scroll down and select LMA.Licensed_Seats__c
  4. Click Apply and then Close to go back to the Lens

Create Formula for License Usage:

  1. Click the + to add another formula column
  2. Enter “License Usage” for the Column Header
  3. Enter “A/B” in the formula (Make sure to enter as UPPERCASE)
  4. Scroll Down and Select Percent as the Format
  5. Click Apply and then Close to go back to the Lens

Format the Lens

  1. Select the Format icon (the paint roller) to the far right hand side
  2. Ensure “License Usage” is in the Reference column
  3. Then select the “Column” subtab
  4. Click Conditional Formatting and “Add Rule”
  1. Click “Add Bin” so that you have 3 ranges.
  2. Lower range is to 0.6 and the background is grey
  3. Medium range is 0.6 to 0.99
  4. High range is above 0.99 and the background is red with white text
  5. Click Apply
  6. Click the format icon again to shrink the formatting toolbox.

Sort License Usage descending

Click on the downward arrow and select “Sort Descending”

Save the Lens

Save the lens as “License Usage” to your app.

Updated: