How to Use Calculated Fields

In this article, we look at how you can use Calculated Fields to generate better reports for your storefront. 

Note: Calculated Fields are created by our Report Toaster staff. Please contact support@cloudlab.com with the details and we will take care of the rest.

What are Calculated Fields

Much like formulas in Excel, calculated fields take data from one area of Report Toaster, run a custom calculation, and output the data somewhere else. 

For example, Suppose I wanted something to take whatever was in column B of a report and multiply it by 15%.

Maybe it's for tax purposes, perhaps, or to calculate a commission or a tip?

To do this, in Excel I might make a formula like so:  

In other words - take what is in column B, multiply it by 0.15 (15%) and display it in column C.

Of course, Excel can do more than simple addition, subtraction, and other basic math.

E.g. You can use vLookup (and other lookup formulas) to say "If column X contains value Y, put it in field Z' 

This can be very useful to lookup names, dates, times, or whatever you want really.

Calculated Fields can do the exact same thing.

Using a Calculated Field, Report Toaster can:
1.) Lookup the data you have stores in one field (e.g. the total on an order, a person's state, a certain product, notes attached on an order)
2.) Run a certain function (e.g. if X is here, do Y) and
3.) Put the output in it a specified custom column. 

Let's see how this works in practice. 

Setting up Calculated Fields

Due to the complexities involved in creating calculated fields, setting them up is currently only possible on the back-end.

Therefore, to get a calculated field setup for a report, all you need to do is:
1.) Sign up to a paid subscription for your store (you can sign up at any time here) 
2.) Contact us at support@cloudlab.com and tell us which fields you are looking to create and which report(s) you're looking to put the fields in.
3.) We'll take care of the rest. 

Example 1 - Simple Math(s) 

For instance - suppose you wanted to create a simple 30% commission based on the Total Sales of your recent Orders? 

Much like our example above then, we can see how such a calculation looks in Report Toaster: 

Here we can see how a calculated field can be used to create an affiliate fee column by working out 30% of the Gross Sales.  

Note - The columns being used as inputs for the formulas don't actually have to be included as columns in the report for the calculated fields to work.

This report would be very useful for displaying what you might owe to an affiliate without working out the calculations manually every time. 

Example 2 - Translating Staff/Location/Device ID's 

For instance - translating fields that are a string of numbers of an ID of some description into something more useable.

This can be quite useful with say, the Staff ID - where Shopify assigns an ID number to each staff member that store owner's are unlikely to know off-hand. 

Here's something similar we created for our demo store: 

As you can see here, the calculated field takes the Staff ID, maps it to a specific Staff Name, and then displays in the Staff Name column. 

Example 3 - Taxes

For instance - you need very specific fields to fill out a tax return. 

Certain jurisdictions might require that you include shipping as part of your taxable total. This is easy to do using a calculated field: 

Similarly, Canadian customers might need to split the taxes column into GST and PST respectively: 

Much like our simple math section above, Report Toaster can use calculated fields to take data from any field, run calculations, and display the outputs separately into their own columns.

Other Examples 

We've come up with several examples of how calculated fields can be used, but here's a few more possibilities. 

1. Taking custom information out of the Note Attributes and Line Item Properties and displaying it in its own columns. (e.g. Monograms, additional variant product options, delivery details, and so on. Especially great for Variant Product Options users!). See here for more info.
   
2. Categorizing data based on tags (e.g. taking date tags and calculating the day of the week for a daily fulfillment report. Especially great for Zapiet and Order Tagger users!)
3. Working out and displaying fixed shipping rates or costs. (e.g. mark anything with UPS in the Delivery Method as a $7.00 shipping cost) 
4. Displaying the age of an order in days. (e.g. a running day counter that starts when the order is submitted and ends when it's been fulfilled).
   

The possibilities are truly limitless. We can take just about any data field in the app, run some calculations, and put it all together to display in a custom column/filter accordingly. 

 

Where Next?

If you would like us to create some custom calculated fields for your account, you can email us at support@cloudlab.com.

All you need is a paid subscription for your store, then let us know what sorts of fields you're looking for and what data to incorporate and we'll take care of the rest. 

• Related Articles

• Reporting on Line Item Properties and Note Attributes

  What are Line Item Properties & Note Attributes In Shopify, there are two particular fields that are contain a additional information relating to an order. These are: Line Item Properties - namely, the additional information associated with the ...

• Line Item Properties/Note Attributes as Columns (Shorts)

  Learn the basics of how to include Line Item Properties or Note Attributes in a report in <5 minutes! (Be sure to switch to Full Screen and high resolution for best results. Refresh your browser if the above embedded video doesn't display, or view ...

• Taxes - Frequently Asked Questions

  We get a lot of tax-related questions on our support chat, so in the interest of sharing that knowledge with the wider Report Toaster community, we thought it was time we compiled an FAQ for all questions pertaining to taxes. Note - A good place to ...

• What's New? - June 2022

  Summer is here, and with it comes a scorching hot new feature in Report Toaster. Calculated Fields are here!  ("Yes, finally!" I hear some of you saying. Quite literally in fact - a few of you even emailed to tell us!) Not only has this been one of ...

• How to use "Now" Fields

  This guide will go over how to use "Now" fields in Report Toaster. What are "Now" Fields? Long time users of Report Toaster may be aware that most of the attributes available on Sales reports show what was true at the time of sale. In contrast, a ...