Data Viz: Why Won’t My Calc Work?!?

In working with Data Visualization Desktop, I’ve gotten frustrated more than once with my data not calculating correctly, or, really, the way I think it should be. Let’s take an example today from my test database.

I love the mapping capabilities of DVD, so I added CITY and STATE columns to my GARMIN_DATA table today. To test out my new mapping columns, I decided to see which state had the highest average Training Effect (impact of an activity on your aerobic fitness per Garmin). After I loaded the data, I created a custom calculation, Avg Training Effect.

However, once I added the new calculation and State to my visualization, each state I have training recorded in shows 50.31.

…Weird because the Training Effect (TE) scale is from 0-5.

Hmm. That is not correct. I wanted to check the logs to make sure that the calculation was correct…

I checked in the obis1-query.log (located at C:UsersszumbrumAppDataLocalTempDVDesktoplogsOBIS) and the query looked correct to me.

Next, I tried to ‘force’ an average via the custom calculation.

When the data recalculated, I got the same result. What the heck?!

At this point, forget the map. I am concerned about DVD’s fuzzy math. Time to focus on the number…the *correct* number.

The data I’m using in DVD is in an Excel spreadsheet. I also have a copy of the same data in cloud, so I do the same calculation on my data via SQL…

I get the number I expected: 3.21.

Why isn’t my data showing? To double-check by state, I ran those numbers, too.

Yes, also correct. What gives?

I decided to go back to my data in DVD to see how I have it loaded. I right-click on my data source and chose “Inspect”.

If I change “Sum” to “Average” for TRAININGEFFECT, what happens?

I get the correct answer. However, I don’t always want the data to be averaged…

I decided to change the column from a Measure to an Attribute. No dealing with Sum, Avg, Min, Max, Count, or Count Distinct for the Measure type.

What does my calculation do now?

Stays the correct answer.

Let’s go back to the map…does it now show different values for each of the states in my table?

Well, yes and no.

At first it didn’t work, but a refresh of the data showed the correct values across the states. SCORE!

But the yellow and brown is ugly.

So I right-click on the canvas and choose Color -> Reset Visualization Colors

Better.

Moral of today’s blog post? If the data doesn’t look right, you may need to play around with the data settings, just like a database.

DVD does a pretty good job of picking out if a column is an Attribute or Measure, but it’s not perfect. You may also need to play around with the Measure types. It pays to be familiar with data settings, whether or not you are IT or functional!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s