Join Robin Hunt for an in-depth discussion in this video The data we don't have, part of Learning Data Analytics.
- So, what about the data we don't have? In a perfect world, all the data we ever wanted exists and is stored somewhere for us to access it. Well, I hate to tell you, the world is not perfect, and some data just doesn't exist. So what do you do when the data is not there? I would recommend that you try to find a way to calculate it through functions or formulas. So, what formulas should you learn? The ones you need you really won't know until you need them, just as an FYI.
But you want to make sure you know basic math functions like add, subtract, multiply, and divide. Other recommendations are things like concatenate, IF, and/or case statements, just depending on what software you're using. Learning how to concatenate allows you to tie fields together to create a single field. I commonly use concatenate for creating a single field for first and last names. It's easier for me to work with it.
I can still sort by first and last name, but I can display my new concatenated field. If and case statements allow you to return values on a condition. This can be hugely helpful when you're reporting. So, here's an example of an IF not of syntax, but just IF in concept. So IF the [State] = X, then the [TaxRate] = Y, and then everything else will default to Z. So there's an IF example for you. You know that things change based on different pieces of your data, an IF or case statement can be great.
Remember again, this is an example for concept, not for syntax. Also important to understand that data won't always be stored the way that we need it, so learning how to convert to other data types can be helpful. You can almost convert anything that's stored, so learn conversion formulas and functions. Math works with numbers and dates, and conversions are really simple to do, but math needs numbers and dates need dates.
If a data's stored as text and you need to calculate dates, you must convert it. If the amount is stored as text and you need it to be currency, then you must convert it. So again, the data we don't have, we can recreate it through calculations.