The last two lessons introduced the concept of DACs context and demonstrated how formulas calculate
differently, based both upon how you have laid out your table and structured your formulas.
One thing that probably jumped out to you is how cramped and difficult those formulas seem to be.
Remember this cash on hand measure?
Relatively speaking, this tax formula is pretty basic.
However, it's still difficult to read and manage the parentheses alone are daunting, but never fear
we can break our measures out into multiple steps using variables.
I can break this formula into three distinct easy to follow steps that separate out our logic, but
before I do so, let me introduce the VA and return keywords using a new simple measure.
This new measure will add one and two together to form three, as you know, I could do this by simply
inputting one plus two.
But let's imagine that one and two are highly complex calculations that look more like our cash on hand
formula than just one or two.
I'm going to break this out into a pair of variables that we then add together separately.
My first line will start with a VA keyboard, followed by a name I like to proceed my variable names
with an underscore for reasons that I will explain shortly, but this is not required.
After the variable name, I set an equal sign and I can write a formula to define that variable.
For the simple measure, I'm going to type var underscore left value equals one, this statement creates
a left value local variable that is equal to one.
I can only use this left value within this measure, no other measures will be able to see it.
So you can think of it as temporary.
I'm going to add another variable var underscore right value equals two on the next line.
I now have two variables within my measure.
If I check my formula, I get an error, since the formula doesn't know what the result should be and
tells us that the expression appears to be incomplete.
I need to tell the formula what value to return, which will involve the return keyword.
I'll start my next line with return and go immediately into writing whatever formula it is that I want
to return as my result.
In this case, I'm going to type underscore and you'll see the entire text pop up, since my measures
table is preceded by underscore, along with my local variables, this essentially contains a list of
the possible items that I may want to return.
For this particular case, I want underscore left values for all, type it out, and then you'll see
that the local variable definitions take on a blue text color in the tax formula.
Ed.
I'll add a plus sign and then underscore right value to finish out my logic.
Now, when I click on check formula, it tells me that the formula is correct and I can move on.
The measure is complete, but I personally prefer not to include any logic in the return line, so I
would rewrite this measure with yet another variable.
Far underscore result equals underscore left value, plus underscore right value.
I can replace my return logic with simply underscore result.
So I've just gone through this process to create a really complex formula to add one and two together,
but how can I use this to improve our process for some of our past formulas?
If you remember from our past videos, we did try using a separate measure for the Max calendar date
to simplify our cash on hand, but it didn't work.
Because our local variables are calculated within the same context, we can use a local variable to
simplify the logic the way that we'd wanted to.
Additionally, if you happened to be building this from scratch, variables are a great way to plot
out and manage complex logic involving your context management.
This is particularly true for measures involving the functions that may involve multiple sub filtered
sets that are cross joined together.
The first logical component I'm going to define here is the end of my time frame, so I'll create a
VA underscore and of time frame equal to max calendar date.
This will capture the maximum calender date in the context for this measure, unadjusted for any other
filter adjustments.
The next logical component I want is the adjusted calendar period, the context for our calendar at
the start of this measure is whatever subset of dates falls within the scope of our current sell.
However, to calculate our cash on hand correctly, we need to include all of the dates that occur prior
to the first date of our scope, in addition to those dates within our scope.
We did this originally with a filter all and then filtered subject to the maximum calender date.
I can perform this step within another variable, using the filter function almost identically to how
we did it before var underscore adjusted Callendar equals filter all calendar and then subject to the
calendar date being less than or equal to the underscore and of our time frame variable.
These two variables have broken out our logic, so that is now easier to follow step by step without
digging through all of the parentheses.
I can finalize the measure by defining my VA underscore result as the calculate profit subject to the
adjusted calendar.
This formula is longer and more worthy than our previous one, but I have compartmentalized the logic
into smaller and easier to understand tidbits.
Using local variables won't always be the right answer.
There are some cases where having separate measures has its own benefits.
However, local variables can allow you to greatly simplify code from something that is simply unreadable
and incredibly difficult right into much more manageable chunks.
Here is one example.
This particular chunk of DACS is from a project of my own whereby I needed to identify how many customers
with active contracts would opt to upgrade their contract from one type to another, given different
probabilities of converting at different times based upon how old their contract is.
Technically, this could have been done in one line of code, but that would have been incredibly difficult
to write and manage.
Initially, you may not find these variables very useful, however, as you progress in the more advanced
DACS computations like the one here, you'll find yourself using them more and more frequently.