Analytics Calculations: Compound Calculations, some best practices
josephmckeown
Posts: 44Moderator, Lightspeed Staff moderator
*Note, from this part of the Discussions, we will assume that you're comfortable with a few points of Calculations: dimensions and measures, filters, finding the "Calculations" button, finding dimensions and measures in Calculation fields, some basic starter calculations: addition, subtraction, division, multiplication, greater than, less than, equal to, single-criteria IF functions, and multiple criteria IF functions.
If you're in the early stages of your Analytics mastery, you may want to take a look at the
earlier discussions
-There is a quote somewhere that goes "Being happy is simple, being simple is complicated". I think that this is attributed to the Dalai Lama, but gosh, finding an accurate source online is, well, complicated. Simple looking designs and simple looking reports can often take lots of agonizing tries, lots of head-scratching, lots of checking with other data people, then lots of going back and trying again.
We'll be looking at some of the tools and toys that you can find in calculations to turn raw data into meaningful metrics, soon.
But first:
One mistake at a time, often repeated, I've taken on these rules to (hopefully) help not lose work either saving it away, by losing my way in a complex calculation, or by trying to figure out a year later what I was doing in an un-labelled calculation.
This is the most efficient way to lose hours, days, even weeks of report building. Of course, you need to save changes to reports. The best way is to select the "Save as New" button and not the button to "Save Changes". If your changes remove any of the dimensions, calculations, or logic on your report, once saved, there is no way to recover the previous one.
For your best practice, always use the 'Save as New" option. This will create multiple versions of the report in its progress, but you can delete these slowly and consciously once your desired report is working the way you want.
Too many calculations can prevent a report from launching. After this it cannot be opened again. As your report becomes more complicated, save a version of it at each step so that if the most recent one breaks the whole report, you have lighter versions to go back to.
When saving a report, with each subsequent version, raise its number in sequence. E.g.:versions 1,4, 1.5, 1.6, etc.
Also, make use of the "description" field when saving. This will make it easier to look at reports later and determine which one is closest to what you're looking for without opening up each report one by one.
Calculations can get several layers deep, take for example this one we put together to turn a full category into just the first-level subcategory:
substring(${categories.full_path_name},(0),(if((position(${categories.full_path_name},"/"))=0,(length(${categories.full_path_name})),if((position((substring((${categories.full_path_name}),(if((position(${categories.full_path_name},"/"))>0,(position(${categories.full_path_name},"/")+1),(length(${categories.full_path_name}))+1)),(length(${categories.full_path_name})))),"/")=0),(length(${categories.full_path_name})),((position(${categories.full_path_name},"/"))+(-1)+(position((substring((${categories.full_path_name}),(if((position(${categories.full_path_name},"/"))>0,(position(${categories.full_path_name},"/")+1),(length(${categories.full_path_name}))+1)),(length(${categories.full_path_name})))),"/")))))))
It's meaningless to read and no one could write it from scratch. Trying to find an error in it is a nightmare, but its composition is based on simple calculations that are eventually copy+pasted into larger ones. The small, larger, and ridiculously large all follow the same simple structure.
If you use multiple fields to grow calculations, instead of editing the fields that the calculations are currently in, this will save you the agonizing process of looking for errors in spaghetti and re-creating your work from scratch.
Prepare the components of your calculations in fields above, then start the simple framework of your compound calculation. e.g.
Our vice-president of data, Cherry Miao often starts her instructions on spreadsheets using what she calls the "Being hit by a bus" principle:
Tempting as it is to leave the Calculation titles as "Calculation 3", "Calculation 6", "Calculation 12", treat yourself to the luxury of titles that explain the steps, and make it clear what each part is expected to do. Future-you will thank Now-you.
The real estate in the Calculations field is somewhat constricting.
If your calculation follows a pattern (as multiple-IF-statements may), prepare the copy+paste logic in a text-editor to make the process cleaner to follow
The order of functions in a calculation is not determined by where there are, necessarily. In complex functions, certain actions are always applied before others, no matter where they occur in the function.
The order of application is: Brackets, Exponents, Division, Multiplication, Addition, Subtraction.
A simple illustration of the error is in finding profit margins, which is profit divided by sales total. This is commonly constructed as:
If our cost was $5, and our sales total was $10, the above calculation would show that our margin is 950% This is because the calculation did the division first (5/10 = .5), then the subtraction, (10-.5=9.5)
What we need to do is prepare our calculation like this:
So that it does the brackets first: (10-5=5), then the division: (5/10=.5, or 50%)
There have been instances where part of what I've tried to create in a calculation may already be achievable using filters, dimensions, or some other preparation built in to Analytics. It is my hope that through these discussions, I can help bring good questions together to see what kind of solutions we can find together.
If you're in the early stages of your Analytics mastery, you may want to take a look at the
earlier discussions
-There is a quote somewhere that goes "Being happy is simple, being simple is complicated". I think that this is attributed to the Dalai Lama, but gosh, finding an accurate source online is, well, complicated. Simple looking designs and simple looking reports can often take lots of agonizing tries, lots of head-scratching, lots of checking with other data people, then lots of going back and trying again.
We'll be looking at some of the tools and toys that you can find in calculations to turn raw data into meaningful metrics, soon.
But first:
One mistake at a time, often repeated, I've taken on these rules to (hopefully) help not lose work either saving it away, by losing my way in a complex calculation, or by trying to figure out a year later what I was doing in an un-labelled calculation.
1: Never "Save Changes"
This is the most efficient way to lose hours, days, even weeks of report building. Of course, you need to save changes to reports. The best way is to select the "Save as New" button and not the button to "Save Changes". If your changes remove any of the dimensions, calculations, or logic on your report, once saved, there is no way to recover the previous one.
For your best practice, always use the 'Save as New" option. This will create multiple versions of the report in its progress, but you can delete these slowly and consciously once your desired report is working the way you want.
2: Save often
Too many calculations can prevent a report from launching. After this it cannot be opened again. As your report becomes more complicated, save a version of it at each step so that if the most recent one breaks the whole report, you have lighter versions to go back to.
3: Save with Version Numbers and Descriptions
When saving a report, with each subsequent version, raise its number in sequence. E.g.:versions 1,4, 1.5, 1.6, etc.
Also, make use of the "description" field when saving. This will make it easier to look at reports later and determine which one is closest to what you're looking for without opening up each report one by one.
4: Grow a calculation in multiple steps
Calculations can get several layers deep, take for example this one we put together to turn a full category into just the first-level subcategory:
substring(${categories.full_path_name},(0),(if((position(${categories.full_path_name},"/"))=0,(length(${categories.full_path_name})),if((position((substring((${categories.full_path_name}),(if((position(${categories.full_path_name},"/"))>0,(position(${categories.full_path_name},"/")+1),(length(${categories.full_path_name}))+1)),(length(${categories.full_path_name})))),"/")=0),(length(${categories.full_path_name})),((position(${categories.full_path_name},"/"))+(-1)+(position((substring((${categories.full_path_name}),(if((position(${categories.full_path_name},"/"))>0,(position(${categories.full_path_name},"/")+1),(length(${categories.full_path_name}))+1)),(length(${categories.full_path_name})))),"/")))))))
It's meaningless to read and no one could write it from scratch. Trying to find an error in it is a nightmare, but its composition is based on simple calculations that are eventually copy+pasted into larger ones. The small, larger, and ridiculously large all follow the same simple structure.
If you use multiple fields to grow calculations, instead of editing the fields that the calculations are currently in, this will save you the agonizing process of looking for errors in spaghetti and re-creating your work from scratch.
5: Start the next-step-calculation with brackets
Prepare the components of your calculations in fields above, then start the simple framework of your compound calculation. e.g.
if(()>(),(),())
6: Label everything
Our vice-president of data, Cherry Miao often starts her instructions on spreadsheets using what she calls the "Being hit by a bus" principle:
"Proceed so that If you were hit by a bus tomorrow, we would be very sad, but we would be able to understand your data"
Tempting as it is to leave the Calculation titles as "Calculation 3", "Calculation 6", "Calculation 12", treat yourself to the luxury of titles that explain the steps, and make it clear what each part is expected to do. Future-you will thank Now-you.
7: Build on TextEdit
The real estate in the Calculations field is somewhat constricting.
If your calculation follows a pattern (as multiple-IF-statements may), prepare the copy+paste logic in a text-editor to make the process cleaner to follow
8: Remember (or take a look at) B.E.D.M.A.S.
The order of functions in a calculation is not determined by where there are, necessarily. In complex functions, certain actions are always applied before others, no matter where they occur in the function.
The order of application is: Brackets, Exponents, Division, Multiplication, Addition, Subtraction.
A simple illustration of the error is in finding profit margins, which is profit divided by sales total. This is commonly constructed as:
sales total - cost / sales total
If our cost was $5, and our sales total was $10, the above calculation would show that our margin is 950% This is because the calculation did the division first (5/10 = .5), then the subtraction, (10-.5=9.5)
What we need to do is prepare our calculation like this:
(sales total-cost)/sales total
So that it does the brackets first: (10-5=5), then the division: (5/10=.5, or 50%)
9: Ask for help
There have been instances where part of what I've tried to create in a calculation may already be achievable using filters, dimensions, or some other preparation built in to Analytics. It is my hope that through these discussions, I can help bring good questions together to see what kind of solutions we can find together.
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ