Step By Instructions On Calculated Fields
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 3
Numeric Calculations
Connect to New data set for numeric calculation –“Numeric Calculation”
Example 1: How to convert Null values to Zeros using calculated field
Drag State to Rows
Targets to Columns. Show labels.
Notice blanks
o Create calculated field zn([Targets]) Cal it Corrected targets
Example 2: To view budget Vs sales for each state –using bullet graph
Step 1: Create Calculated field: sum([Sales]) -sum([Corrected Targets]) call it
“Salesless targets”
Step 2: Create bullet chart –CNTRL (Sales, Corrected targets & state)
Step 3: Swap Axis if required
Step 4: Drag Calculated field ““Salesless targets” to Colors
String Calculations:
Connect to Data Set: Sample - Superstore Subset (Excel)
Select - Orders Sheet
Example 1: (Adding 2 strings fields)
Step 1: [Customer Name] + ' ' + [Customer Segment]
o Drag this to Rows to test if the calculation works
Example 2: (Adding 2 String fields and a numeric field)
Step 1: [Customer Name] + ' ' + [Customer Segment] + ' ' [Order ID]
Notice error in calculated field. Need to convert numeric value [Order ID] to string by
using Type conversions
Step 2: [Customer Name] + ' ' + [Customer Segment] + ' ' + str([Order ID])
o Drag and drop this to the Rows to test it
Example 3: (How to Separate first name from last name) –By using the space
Step 1: Drag Customer Name to Rows. Understand how Space differentiates the first from last
names
Step 2: To find the character value that will take you to the space. Create calculated field
“Finding Space”:
o find([Customer Name], ' ')
Step 3: Create another calculated field “First name”:
o LEFT([Customer Name],[Finding Space]-1)
Date Calculations:
Example 1: How to convert a string into a date field
Connect to excel file “Date Calculation” and select Sheet “Date as Integer”
Step 1: Drag and drop Date to rows and understand the way it is arranged
Step 2: Logic applied:
o To convert the Integer into a string
o Break the string into three using, left, Mid & Right function
o Reconstruct the three parts of the date as integer Using Makedate() function,
arrange them as proper dates
Step 3: Create Calculated field “Date V1” : str([Date])
Step 4: Create Calculated field “Year”: (left([Date V1],4)
Step 5: Crate Calculated field “Month”: (mid([Date V1],5,2)
Step 6: Create Calculated field “Day”: (right([Date V1],2)
Step 7: Convert all the three fields into integers again using Type conversion
int(Year),
int(Month)
int(Day)
Step 8:Reorganize the three fields using the MAKEDATE function. Calculated field:
MAKEDATE([Year],[month],[Day])
Step 9: Test and see if the calculation is valid and you have a valid date field.
Example 2: Question is how many days does it take to ship orders my order?? Idea is to
subtract two date fields Order date & ship date and get the output in terms of no of
days.
Connect to Data Set: Sample - Superstore Subset (Excel)
Select - Orders Sheet
Step 1: Create a Filled map using “State or Province”
Step 2: Create calculated field datediff('day',[Ship Date], [Order Date])
Step 3: Drag and drop calculated field to color
Step 4: Now it shows Sum (Calculated field). Change it to Avg(Calculated field)
Step 4: Edit Color to “Red to green “and reverse it.
Logical Calculation:
Example 1: How to reclassify states with regions (Using CASE Function)
Step 1: Create filled map using “State or Province”
Step 2: Drag “Region” to Colors
Step 3: To convert Texas from “Central” to “South” ; Michigan from “Central” to “East”
Step 4: CASE [State] when "Texas" then "South" when "Michigan" then "East" else [Region] end
Step 5: Drag this calculated field to Colors
Example 2: How to reclassify states with regions (Using IF Function)
Step 1: Create filled map using “State or Province”
Step 2: Drag “Region” to Colors
Step 3: To convert Texas from “Central” to “South” ; Indiana from “Central” to “East”
Step 4: IF [State] = ‘Texas’ Then ‘South’
ELSEIF [State] = ‘Indiana’ then ‘East’
ELSE [Region]
End
Step 5: Drag this Calculation to Colors
