Step By Instructions On Calculated Fields

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 3

DownloadStep By Instructions On Calculated Fields
Open PDF In BrowserView PDF
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
Source Exif Data: 
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.4
Linearized                      : No
Page Count                      : 3
Language                        : en-IN
Tagged PDF                      : Yes
XMP Toolkit                     : 3.1-701
Producer                        : Microsoft® Word 2013
Creator                         : 朱熠锷
Creator Tool                    : Microsoft® Word 2013
Create Date                     : 2015:12:07 10:18:19+05:30
Modify Date                     : 2015:12:07 10:18:19+05:30
Document ID                     : uuid:2799A768-DE03-4C85-8DE3-01F73D21A2D6
Instance ID                     : uuid:2799A768-DE03-4C85-8DE3-01F73D21A2D6
Part                            : 1
Conformance                     : A
Author                          : 朱熠锷
EXIF Metadata provided by EXIF.tools

Navigation menu