Compute Variables in Variable View

Need total spend from online and in-store spend? An overall score from a battery of ratings? A tidy “NSW” from “nsw” and ” NSW ”? Or each respondent’s age from their date of birth? Compute calculates a new variable from the ones you already have, right in Variable View. You pick the variables and an operation, check a live preview against your real data, and click RUN.

Like Recode, there’s no wrangle to build. The new variable sits alongside the originals, which are never changed, ready for Data Explorer, dashboards and everything else that reads the data store.

Understanding Compute

What you can calculate

Compute works one record at a time: each record’s result comes only from that record’s own values. Operations come in three families.

Family	Operations	Result
Numeric	Add, Subtract, Multiply, Divide	A number from two values, in the order you give them
	Sum, Average, Median, Minimum, Maximum, Standard deviation (population), Count nonblank values	A number summarising several variables, such as a battery of ratings
	Absolute value	The number without its minus sign
Text	Concatenate	Several values joined into one, with a separator
	Upper, Lower, Trim, Substring	A cleaned-up or shortened copy of one text variable
Date	Year, Quarter, Month number, Day of month, Hour (24h)	A number taken from a date
	Month name, Weekday, Time of day	Text taken from a date, such as “March”, “Tuesday” or “Morning”
	Age, Difference	The time between two dates

See Operations in detail for exactly how each one works.

Which variables you can use

You can use any variable in the data store except:

a Multi-Response variable, or one of its child variables
a variable whose personal information is set to Mask, Mask + retention or Wipe
a variable you’ve added but haven’t saved yet

Each family needs the right kind of variable:

Family	Works with
Numeric	Integer and Number variables
Text	Text and Long Text variables
Date	Date and Date Time variables

Count nonblank values and Concatenate work with any variable you can select.

Compute works on draft and published data stores.

How a Compute runs

A Compute is static: “Compute runs once on the current data; new rows and source edits require a manual Run again.”

If any of its source variables change after a Compute has run, the output variable is tagged STALE in Variable View: “The source changed after this action ran. Reopen it to run again.” See Reopen or run a Compute again.

Start a Compute

You can start from the toolbar or from a variable’s row.

Open Variable View - On the data store page, click the Variable View tab.
Click Add Variable - The Add Variable dialog asks you to “Choose how you’d like to create your new variable”.
Choose Compute - Click the Compute card, “Calculate or transform values from existing variables.”, then click SELECT. The Compute window opens full screen.

Or, in Variable View, click the Compute Variable icon on the row of a variable you want to use. The Compute window opens with that variable already in Selected Variables.

If the Compute card is locked, the data store has nothing Compute can use: “Compute requires at least one eligible source variable.” See Which variables you can use.

The Compute window has two steps, Source & output and Build expression. Use NEXT and BACK to move between them. NEXT stays disabled until the current step is complete.

Step 1: Choose the sources and output

Choose the sources

Under Choose Your Inputs, drag every variable the calculation needs from Available Variables into Selected Variables: “Drag variables from the left into Selected Variables to bring them into the calculation.” To add two columns together, select both. To average a battery of ratings, select every rating.

The operations you can use in step 2 depend on what you select here, so if you want a date operation, select a date variable.

Define the output

Under Define the Output, choose a Destination: “Where should we save the calculated result?”

New Variable (the default): “Create a new variable for the results”.
Overwrite Existing: “Write the results over an existing variable”. See Overwrite an existing variable.

For a new variable, fill in:

Field	What to enter
New Variable Name	The system name, for example `total_spend`. It must start with a letter and use only letters, digits and underscores, up to 100 characters. It can’t be the name of one of your sources.
Variable Label	The friendly name people see, for example “Total spend”.
Variable Type	Auto (the default), Numeric or Text. See Choose the output type.

If the name is already taken, you’ll see some numbered alternatives. Click one to use it, or type your own.

Click NEXT.

Choose the output type

With Variable Type on Auto, Compute works out the type from the operation, and tells you: “Auto resolves from the operation: numeric.” A Numeric result creates a Number variable, and a Text result creates a Text variable.

To force the result to one type, choose Numeric or Text. Any record whose result can’t be converted is left blank. For example, forcing Upper to Numeric turns “42” into 42, but leaves “NSW” blank.

Overwrite an existing variable

Choose Overwrite Existing to put the results into a variable you already have. Choose it from Overwrite Target, then type its name in Type the target name to confirm.

You can only overwrite a variable that:

was added in Data Wrangler, not by a survey
isn’t one of the Compute’s sources
isn’t the primary key, a multi-response variable or one of its child variables
isn’t set to Mask, Mask + retention or Wipe
matches the output type: Number for a numeric result, or Text for a text result

If nothing qualifies, Overwrite Existing is unavailable.

Step 2: Build the expression

“Pick an operation and operands on the left. The result summary and a live preview against your data update as you go.”

The Expression card has two tabs. Builder lets you click your way to a calculation. Syntax lets you type it, and can do more. See Type the expression in Syntax.

Choose an operation - Under Function, click an operation in the Numeric, Text or Date list. Type in Search operations to find one. Operations you can’t use yet are greyed out, with a reminder such as “Add a date variable in Source & output to use these.”
Set the operands - Choose what the operation works on. Compute fills these in from your selected variables, so check they’re the ones you want. See Operations in detail.
Round the result, if you like - For a numeric result, turn on Round result and choose 0, 1 or 2 decimal places.
Check the preview - See Check the preview.
Click DONE - See Run the Compute.

Under the operands, a line shows the calculation as an expression, for example total_spend = SpendOnline + SpendStore.

Operations in detail

Add, Subtract, Multiply and Divide

Each takes exactly two operands, Operand 1 and Operand 2. For each, choose Column and pick a variable, or Value and type a number. For example, Multiply a price by 1.1.

Order matters: “Order is preserved, so Subtract and Divide read top to bottom.” Subtract gives Operand 1 minus Operand 2, and Divide gives Operand 1 divided by Operand 2. To swap them, click Move up on Operand 2.

If either operand is blank, the result is blank. Dividing by zero leaves the result blank and counts as a failed row. See Failed rows.

Sum, Average, Median, Minimum, Maximum and Standard deviation

These summarise all your selected numeric variables, “separately for each row”. There are no operands to set: the window lists the variables it will use.

Blanks are skipped, so the Average of 4, 5 and a blank is 4.5. If every value is blank, the result is blank.
Standard deviation (population) divides by the number of values (n), not n − 1. A record with only one value gets 0. A record whose answers are all the same also gets 0, which makes it handy for spotting straightlining.

Count nonblank values

Counts how many of the selected variables have a value in each record. It works with any type of variable, and a record with nothing gets 0 rather than a blank. Use it to see how many questions in a battery each respondent answered.

Absolute value

Removes the minus sign from one numeric variable, so −3 becomes 3. Use it after a Subtract when you care how big a change is, not which way it went.

Concatenate

Joins several values into one piece of text, in the order you list them. Compute starts with all your selected variables. Use + Add operand, Move up and Remove to change the list. Numbers and dates are turned into text.

Choose a Separator to go between the values: Space (the default), Comma + space, Hyphen, None or Custom. For Custom, type it in Custom separator.

“Empty inputs and their separators are skipped. Blank text still counts as a value.” So joining “Sydney”, a blank and “NSW” with Comma + space gives “Sydney, NSW”, not “Sydney, , NSW”.

Upper, Lower and Trim

Each works on one text variable.

Upper and Lower change the case, so “nsw” becomes “NSW” or “nsw”.
Trim removes spaces from the start and end. Under Remove whitespace, choose both (the default), leading or trailing.

A value that’s only spaces is treated as text here, not as a blank, so trimming it gives empty text.

To fully clean a value, such as ” nsw ” into “NSW”, you need both Trim and Upper. Type it in Syntax as UPPER(TRIM(State)).

Substring

Takes part of a text value. Set Start position (1-based), where 1 is the first character, and Length (blank = to end). For example, start 1 and length 2 take the first two characters of a postcode.

If the start is past the end of the value, the result is empty text.

Year, Quarter, Month number, Month name, Day of month, Weekday, Hour (24h) and Time of day

Each takes one date variable and gives you part of it.

Time of day puts each time into a bucket: “Night 00:00–05:59 · Morning 06:00–11:59 · Afternoon 12:00–17:59 · Evening 18:00–23:59”.

Age

Gives the number of completed years between two dates. Set Date of birth to a date variable, and Reference / end date to one of:

Column: another date variable
Today (the default): the date the Compute runs
Row load timestamp: the date each record was loaded into the data store. If a record has no load date, its result is blank.

Difference

Gives the time between a Start date and a Reference / end date, in completed units. Choose the Elapsed unit: hours, days (the default), weeks, months, quarters or years. Either date can be a Column, Today or Row load timestamp.

“End minus start, in completed units. Reversed dates return negative results.” Age and Difference don’t have a Move up button, so to reverse a difference, swap the dates you choose.

Days and weeks are counted in calendar days, so a difference that crosses a daylight saving change isn’t a day short.

Time zones

Date operations use your computer’s time zone, the same one Data View shows Date Time values in, so an Hour or Day of month matches the Source value you see. The window shows which, for example “Time zone: Australia/Sydney. Date-only values keep their calendar date.”

The time zone is saved with the Compute when you create it, so it gives the same results whoever runs it again, wherever they are.

Check the preview

As you build, two panels update from a sample of up to 1,000 of the data store’s newest records. The sample doesn’t use any Data View filters.

Panel	What it shows
Result Summary	For a numeric result, the sample’s Count, Mean, Min and Max, and a Distribution chart. For text, the Count and the number of Distinct values. Underneath, how many results are blank (“null”) and how many failed.
Live Preview	Each sample record’s Source values, the Output it gets, and a Reason when the output is blank. Above the table, for example, “Newest 1,000 of 5,230 records · stamped once (static).”

A blank source value shows as “(blank)”, and a blank result as “(null)”.

If the preview can’t load, you’ll see an error with a Retry preview button.

Failed rows

A result can be blank for two reasons:

A source value was blank. The Reason says “Missing input”. That’s expected, and it doesn’t stop you running the Compute.
The calculation failed for that record. The Reason says why: “Division by zero”, “Input has an invalid number or date”, “Result is outside the supported range”, “Result cannot be converted to a finite number” or “Date is outside the supported range”.

If any sample records failed, a checkbox appears under Live Preview, for example “I acknowledge the 3 rows this derivation failed on.” Tick it to show you’ve seen them. DONE stays disabled until you do. Those records will be blank in the output.

Type the expression in Syntax

Click the Syntax tab to see your calculation as an expression, such as SpendOnline + SpendStore, and edit it directly. “Edits here drive the Compute.”

Syntax can do things Builder can’t, because operations can go inside each other:

You want	Type
Clean up a state name	`UPPER(TRIM(State))`
Add three spend columns	`SpendOnline + SpendStore + SpendPhone`
Average, then round	`ROUND(AVERAGE(Q1, Q2, Q3), 1)`
Spend per visit, as a percentage	`Spend / Visits * 100`
Build a label	`CONCAT_WS(" - ", Region, UPPER(Store))`

Use variable names, not labels, without quotes. They aren’t case-sensitive: if you type spendonline, it’s changed to SpendOnline when you switch to Builder and back.
Put text in double quotes, such as " - ".
* and / are worked out before + and -. Use brackets to change that, for example (Q1 + Q2) / 2.
Variables you use in Syntax become the Compute’s sources, even if you didn’t select them in step 1.

The Functions and Operators lists under the editor show everything you can use.

Function reference

Function	What it does	Builder operation
`SUM(a, b, …)`, `AVERAGE(…)`, `MEDIAN(…)`, `MIN(…)`, `MAX(…)`, `STDEV(…)`	Summarise numbers, skipping blanks	Sum, Average, Median, Minimum, Maximum, Standard deviation (population)
`COUNT(a, b, …)`	Count values that aren’t blank	Count nonblank values
`ABS(x)`	Remove the minus sign	Absolute value
`ROUND(x, places)`	Round to 0, 1 or 2 decimal places	Round result
`UPPER(x)`, `LOWER(x)`	Change case	Upper, Lower
`TRIM(x, both)`	Remove spaces: `both`, `leading` or `trailing`	Trim
`SUBSTR(x, start, length)`	Part of a text value. Use `END` as the length to go to the end.	Substring
`CONCAT_WS(separator, a, b, …)`	Join values with a separator, skipping blanks	Concatenate
`CONCAT(a, b, …)`	Join values with nothing between them	Syntax only
`YEAR(d)`, `QUARTER(d)`, `MONTHNUM(d)`, `MONTHNAME(d)`, `DAY(d)`, `WEEKDAY(d)`, `HOUR(d)`, `TIMEOFDAY(d)`	Part of a date	Year, Quarter, Month number, Month name, Day of month, Weekday, Hour (24h), Time of day
`AGE(birth, reference)`	Completed years. Leave out the reference to use today.	Age
`DATEDIFF(start, end, days)`	Time between dates: `hours`, `days`, `weeks`, `months`, `quarters` or `years`	Difference
`TODAY()`, `LOAD_TIMESTAMP()`	Today’s date, or the record’s load date	Today, Row load timestamp
`NUMERIC(x)`, `TEXT(x)`	Convert the result	Variable Type Numeric or Text

Builder and Syntax together

A simple expression, such as Q1 + Q2 or AVERAGE(Q1, Q2, Q3), shows in both tabs, and a change in either shows in the other.

An expression with one operation inside another, such as UPPER(TRIM(State)), can only be changed in Syntax. Builder says “This expression is nested, so the Builder cannot show it. Edit it in Syntax, or pick an operation below to replace it.” Picking an operation in Builder replaces your typed expression.

Fix mistakes

When something’s wrong, it’s underlined in the editor, and a box headed “Fix these before continuing:” lists each problem with where it is, for example “Line 1, column 7 — “Q9” is not a column in this data store.” DONE is disabled until they’re fixed, on both tabs, with the message “Fix the errors in the Syntax view.”

To give up on your edits, click Revert to the Builder expression. The errors clear and DONE works again.

Example: total spend

To add online and in-store spend into a new variable:

Choose the sources and output - Drag SpendOnline and SpendStore into Selected Variables. Keep New Variable, set New Variable Name to total_spend and Variable Label to “Total spend”, and leave Variable Type on Auto. Click NEXT.
Choose Add - Under Numeric, click Add. Check Operand 1 is SpendOnline and Operand 2 is SpendStore.
Check the preview - Records with a blank in either variable get a blank total, with the reason “Missing input”.
Click DONE, then RUN - The new variable is created and the Compute starts running.

Run the Compute

Click DONE - The Confirm and Run window sums up what will happen: “Create one new variable.” (or “Replace the confirmed target’s values.” when overwriting), the Variable, Label, Type and Evaluation (“Static · run once”), the expression, and how many sample results are blank. Rows outside the sample may also be blank.
Click RUN - You’ll see Compute queued: “Compute is queued and will continue in the background.” The output variable is tagged RUNNING in Variable View: “This action is executing.” You can carry on working, or leave the page. To change something first, click Cancel instead: you go back to the Compute window with everything still filled in.
Check it finished - A Compute complete message tells you, for example, “Updated total_spend. Variable View has been refreshed. 12 null results.”

The new variable shows a COMPUTED marker next to its name in Variable View: “Derived with Compute. Reopen the action to view its configuration.” Your source variables aren’t changed.

DONE stays disabled until the preview has loaded, the expression has no errors in either tab, and you’ve ticked the failed rows checkbox if there is one.

Reopen or run a Compute again

Reopen a Compute to change its calculation or Variable Label, or to run it again after a source has changed (it’s tagged STALE).

Open the Compute - Click the Reopen Compute / Run again icon on the output variable’s row, or use Reopen in Manage Actions. The Reopen Compute window opens with the saved settings.
Make your changes - You can change the Variable Label, the operation and the expression. The selected variables, the destination, the name and the Variable Type are locked.
Click DONE, then RUN - The output is recalculated for every record, and you’ll see the same Compute queued and Compute complete messages as for the first run. The STALE tag clears.

To use different variables, or to write to a different variable, start a new Compute.

Manage Actions

Manage Actions, in the Variable View toolbar, lists every Recode and Compute in the data store. A Compute is listed under each of its source variables, with a function icon, its Output and its Status. The statuses are the same as for a Recode. See Manage Actions.

Run again runs the Compute straight away with the same settings. If the data has changed so that some records would now fail, the Reopen Compute window opens instead, so you can check and acknowledge them first.
Reopen opens the Reopen Compute window.

The Audit tab lists each run as “Compute”, with all its source variables.

Computes and Recodes belong to the data store, so they’re only in Manage Actions. They aren’t listed with your wrangles in Data Wrangler, and you can’t share them on their own.

Frequently Asked Questions

Does a Compute update when new data comes in?

No. A Compute runs once, when you click RUN. New records get a blank until you run it again. If a source variable changes, the output is tagged STALE. Reopen it, or click Run again in Manage Actions, to bring it up to date.

Does an Age using Today update every day?

No. Today means the day the Compute ran, and the output isn’t tagged STALE just because time has passed. Run it again whenever you need ages brought up to date.

Should I use Compute or Recode?

Use Compute to calculate a value: a total, an average, an age, cleaned-up text. Use Recode to group or relabel one variable’s values, such as 0–10 scores into Detractors, Passives and Promoters. You can do both: compute an average score, then recode it into bands.

Can I use if/then logic?

Not in Compute. To set a value depending on a condition, use Recode.

Why are some operations greyed out?

You haven’t selected a variable of the right type in step 1. Numeric operations need an Integer or Number variable, text operations a Text or Long Text variable, and date operations a Date or Date Time variable. Click BACK and add one.

Why can’t I see my variable in Available Variables?

It’s a multi-response variable or one of its child variables, it hasn’t been saved yet, or its personal information is set to Mask, Mask + retention or Wipe. See Which variables you can use.

Why is my result blank for some records?

Check the Reason in the Live Preview. “Missing input” means a value the calculation needed was blank. Anything else means the calculation failed for that record, for example “Division by zero”. See Failed rows.

Why are the preview numbers different from my whole data store?

The preview and Result Summary use a sample of up to 1,000 of the newest records. When the Compute runs, it uses every record.

Can I compute on a published data store?

Yes. Creating and editing Computes stays available after you publish.

Can I change a Compute’s variables or type after it’s run?

No. When you reopen a Compute, the selected variables, destination, name and Variable Type are locked. Start a new Compute instead.

Why don’t I see Add Variable, Compute Variable or Manage Actions?

You can only view this data store. If it’s published, the notice at the top of Variable View says “This data store is published and is read-only for you.” Ask the owner for Can Edit access.

Why isn’t my Compute in the Data Wrangler list?

A Compute is part of its data store, not a wrangle, so it isn’t listed in Data Wrangler. Open the data store’s Variable View and click Manage Actions. See Manage Actions.

Common Issues and Solutions

”This operation has no compatible input. Choose another above, or add a variable in Source & output.”

The operation you’ve picked needs a type of variable you haven’t selected.

Choose another operation - Pick one that isn’t greyed out.
Or add a variable - Click BACK and drag in a variable of the right type.

”Choose a column for operand 1.”

An operand is set to Column, but no variable is chosen, or the one chosen doesn’t suit the operation.

Pick a column - Choose a variable from the operand’s Column list.

”Fix these before continuing:”

The expression in Syntax has a mistake. Each line says where it is and what’s wrong, for example “Unknown function “AVG”.” or “Expected a numeric value here, but this is text.”

Read each message - Fix the spelling, brackets, quotes or variable name it points to.
Check the reference - The Functions list under the editor shows the names you can use.
Or start again - Click Revert to the Builder expression.

”Sources changed. Refresh the preview before running Compute.”

Someone changed a source variable after your preview loaded. Nothing has been written.

Close the message - The Compute window reopens on Build expression, with your settings kept, and loads a fresh preview.
Check the new preview - Wait for it to load.
Run it again - Click DONE, then RUN.

”This preview is out of date. Refresh it before running Compute.”

The preview has expired. This happens if the Compute window has been open for more than 30 minutes. Nothing has been written.

Close the message - The Compute window reopens on Build expression, with your settings kept, and loads a fresh preview.
Check the new preview - Wait for it to load.
Run it again - Click DONE, then RUN.

”This is a Data Store action. Open it from the Data Store’s Manage Actions.”

You’ve opened an old link or notification that pointed to a Compute or Recode in Data Wrangler. These now open from the data store instead.

Open the data store - Go to the data store the Compute belongs to, and click the Variable View tab.
Open Manage Actions - Find the Compute and click Reopen. See Manage Actions.

”Compute failed”

The Compute didn’t finish. The message says why, where it can. If it was the first run, no new variable is left behind. If it was a run again, the output keeps its previous values.

Read the message - It may tell you what to fix.
Run it again - Open Manage Actions and click Run again, or reopen the Compute and run it.
Contact support - If it keeps failing, contact support with the data store’s name and GUID (copy it from Settings).

My output is tagged STALE

“The source changed after this action ran. Reopen it to run again.” The output may be out of date.

Reopen the Compute - Click Reopen Compute / Run again on the output variable’s row.
Click DONE, then RUN - The output is recalculated from the current data.