Category Archives: How-To

Recipes for data exploration using Explore Analytics by example.

Show Me Nothing!

Posted on by

Show Me Nothing!

Some relationships can be tough.

Most of the time, when you’re running a report on relational data, you’re trying to see what relationships exist, and with Explore Analytics, that’s fairly easy to do.

What can be more difficult, however, is showing relationships that aren’t there. Because there is no relationship, there’s no data that exists.

Let’s take a specific example: you want to create a view that shows users with no assigned assets.

Fear not! With Explore Analytics, we can create a report that can show you where there is nothing.

What we’re going to do is:

  1. Create a pivot of assets grouped by users – this provides the mapping of assets to users. This will be missing the users with no assets, however.
  2. Create a pivot of all the users – this provides us a list of all the users, including the ones with no assets – it’s going to fill in the gaps of the users with no assets.
  3. Create a mashup of the two views – By putting the two views together, we are able to see any users without assets.

This method works the same whether it’s users with no assets, users who haven’t updated the system, assets without anti-virus, etc.

We’re going to walk through this example with the demo ServiceNow data source:

Create a pivot of assets grouped by users

  1. Create a new view on the Asset table
  2. Type: Pivot
  3. Row Labels: Assigned To
  4. Values: Count (should be auto-generated for you)
  1. Relabel the “Assigned To” field to “Name”. This will ensure that the “User” in this view matches the “User” in the other view.

Create a pivot of all users

  1. Create a new view on the User table
  2. Type: Pivot
  3. Row Labels: Name
  4. Remove “Count” from the Values field, we won’t need it

Create a mashup of both views

  1. Create a new mashup view, with both of the views we just created as an input view
  1. Sort by Count, smallest to largest.

  1. Voila! You should have a list, at the top of which are the users with no assets!

Charts are meant to be interactive

Posted on by

You probably don’t need convincing that interactive charts are far superior to static ones. There are many ways of making charts interactive by allowing the viewer to drill down to the next level of summary, drill-through to details, or simply allowing users to change properties of the chart such as zoom level or scale.

In this short article, I’d like to show you another nifty (simple yet powerful) interaction. This one is designed to let the user choose between focusing on a calculated metric or seeing how the calculation was made. We’ll show it by example.

Consider data about last year’s budget for some fictitious company. Since it’s last year’s, we also have the actual spending and we can analyze the difference between the amount budgeted and the actual amount spent for each account. The data that we have includes the account name (e.g., “Marketing & advertising”), the type (“Budget” or “Actual”) and the amount.

In the process of creating the chart, we define a calculated column called “Overage” that’s simply calculated as “Actual” minus “Budget”.

A positive “Overage” means that we went over budget, and a negative one means that we were under budget.
This chart shows all three “types”: Budget, Actual, and Overage.

This is nice because we can see all the information, but it’s a bit busy and it’s hard to see the overage where it’s small. The solution is elegant, just click on the legend and select what you want to see:

The result of keeping only “Overage” is this:

To try this yourself, click on the above chart to go to the interactive chart.

In Explore Analytics, the legend selection functionality is always available for category and timeline charts, so you can try it today with your own charts.