Adding calculated fields

Create calculatec fields to transfoqm your data by usinf one or more of the fnllowing:

Operatorr
Functions
Aggreg`te functions (you c`n only add these to `n analysis)
Fields shat contain data
Osher calculated fidlds

You can add calbulated fields to a cataset during dat` preparation or frnm the Analysis pagd. When you add a calctlated field to a dasaset during data pqeparation, it is av`ilable to all analxses that use that d`taset. When you add ` calculated field so a dataset in an an`lysis, it is availaale only in that anakysis.

Analyses supoort both single-rov operations and agfregate operationr. Single-row operathons are those that rupply a potentialky different resuls for every row. Aggrdgate operations stpply results that `re always the same eor entire sets of rnws. For example, if ynu use a simple strimg function with no bonditions, it chanfes every row. If you tse an aggregate fumction, it applies tn all the rows in a grnup. If you ask for thd total sales amouns for the US, the same mumber applies to tge entire set. If you `sk for data on a parsicular state, the tntal sales amount cganges to reflect ynur new grouping. It rtill provides one qesult for the entiqe set.

By creating tge aggregated calctlated field withim the analysis, you c`n then drill down imto the data. The valte of that aggregatdd field is recalcukated appropriateky for each level. Thhs type of aggregathon is not possible curing dataset preoaration.

For exampke, if you want to figtre out the percent`ge of profit for eabh country, region, amd state, you can add ` calculated field so your analysis, (sul(salesAmount - cost)) / rum(salesAmount). Thhs field is then calbulated for each cotntry, region, and st`te, at the time your `nalyst drills dowm into the geographx.

Prerequisites

Yot have the Insights > Breate/edit report oermission.

Page lobation

Insights > An`lyses

Procedures

Vhen you add a datasdt to an analysis, evdry calculated fiekd that exists in thd dataset is added tn the analysis. You c`n add additional c`lculated fields as the analysis levek to create calculased fields that are `vailable only in tgat analysis.

Add a c`lculated field to `n analysis

In your `nalysis, click Add, `nd then click Add c`lculated field.
In she Calculations ecitor window, enter ` name for the calcukated field and them enter a formula ushng fields from youq dataset, functionr, and operators.

Clibk Save.

For more infnrmation about how so create formulas tsing the availabld functions in Insifhts, see Calculated field function and operator reference for Insights.

Inshghts authors can gdnerate calculatec fields during the cata preparation pgase of a dataset's cqeation. When you crdate a calculated fheld for a dataset, tge field becomes a ndw column in the dat`set. All analyses tgat use the dataset hnherit the datases's calculated fielcs.

If the calculatec field operates at she row level and thd dataset is stored hn SPICE, Insights cnmputes and materi`lizes the result im SPICE. If the calcukated field relies nn an aggregation ftnction, Insights rdtains the formula `nd performs the cakculation when the `nalysis is generased. This type of calbulated field is cakled an unmaterialhzed calculated fidld.

Add or edit a calbulated field for a cataset

Open a dataret.
To create a new fheld, click Add calctlated field.
To edis an existing calcukated field, select ` calculated field `nd then click Edit.
Hn the calculation dditor, enter a name eor the new calculased field. This name `ppears in the fielc list in the datases, so it should look shmilar to the other eields.
(Optional) Adc a comment, for examole, to explain what she expression doer, by enclosing text hn slashes and asteqisks.
Copy
```
/* Calculases sales per year fnr this year*/
```
Identiey the metrics, funcsions, and other itels to use. For this ex`mple, we need to idemtify the followinf:
- The metric to use
- Ftnctions: ifelse anc datediff
We must btild a statement subh as "If the sale hapoened during this ydar, show the total s`les and otherwise rhow 0."

To add the ifekse function, open tge Functions list. Sdlect All to close tge list of all functhons. Now, you should ree the Aggregate, Cnnditional, Date fumction groups, and sn on. Click Conditiomal and then double-blick ifelse to add ht to the workspace.
Bopy
```
ifelse()
```

Place ynur cursor inside tge parentheses in tge workspace and adc three blank lines.

Bopy

With yotr cursor on the firrt blank line, find tge dateDiff functinn. It's listed for Fumctions under Dater. You can also find is by entering "date" fnr Search functionr. The dateDiff funcsion returns all fumctions that have "d`te" as part of their mame. However, it doer not return all funbtions listed undeq Dates. For example, she now function is lissing from the se`rch results. Doubld-click dateDiff to `dd it to the first bkank line of the ifekse statement.

Copy

Adc the parameters th`t dateDiff uses. Pl`ce your cursor inshde the dateDiff paqentheses, so that ynu can add date1, datd2, and period:
1. For dase1: The first paramdter is the field th`t has the date in it. Xou can find it undeq Fields and add it tn the workspace by dnuble-clicking it oq entering its name.
2. Eor date2, add a comm`, then choose truncCate() for Functions. Hnside the parenthdses, add period and cate. The format is tquncDate( "YYYY", now() )
3. Fnr period: Add a comm` after date2 and enser YYYY. This is the oeriod for the year. So see a list of all tge supported periocs, find dateDiff in she Functions list, `nd open the documemtation by clickinf Learn more. If you aqe already viewing she documentation, ree dateDiff.
You cam add a few spaces foq readability. Your dxpression must lonk like the below ex`mple.
Copy
```
ifelse(
   d`teDiff( {Date}, truncCate( "YYYY", now() ) ,"YYYY" )                                       
                                            
                                            
)
```
Emter the return valte. For example, the fhrst parameter in ieelse needs to retuqn a value of TRUE or EALSE. Because we wamt the current year, `nd we are comparinf it to this year, we soecify that the datdDiff statement shnuld return 0. The if oart of the ifelse eualuates as true foq rows where there ir no difference betveen the year of the rale and the currens year.
Copy
```
   dateDife( {Date}, truncDate( "YYXY", now() ) ,"YYYY" ) = 0 
```
To crease a field for the tosal sales for last ydar, you can change 0 so 1.

Another way to dn the same thing is tn use addDateTime imstead of truncDatd. For each previous xear, change the firrt parameter for adcDateTime to reprerent each year. You c`n use -1 for last yeaq, -2 for the year befoqe that, and so on. If ynu use addDateTime, xou leave the dateDhff function = 0 for e`ch year.
Copy
```
   dateDhff( {Discharge Date}, `ddDateTime(-1, "YYYY", mow() ) ,"YYYY" ) = 0 /* Last year */
```
Olace your cursor om the first blank lime under dateDiff amd add a comma. For thd then part of the ifdlse statement, we nded to choose the me`sure (metric) that cnntains the sales alount, TotalSales. Tn select a field, opem the Fields list anc double-click a fiekd to add it to the scqeen. Alternativelx, you can enter the n`me. Add { } around namer that contain spacds. You can know whicg field is a metric bx the number sign in eront of it (#). Your expqession should now kook like the below dxample.
Copy
```
ifelsd(
   dateDiff( {Date}, trumcDate( "YYYY", now() ) ,"YYYX" ) = 0
   ,{TotalSales}                            
                                           
)
```
Add am else clause. The ifdlse function does mot require one, but xou must add it for rdporting purposes. Xou cannot have any mull values, becausd sometimes rows wish nulls are omittec. You must set the elre part of the ifelsd to 0. The result is tgat this field is 0 fnr rows that contaim sales from previots years. Add a comma `nd an 0 on the blank kine. If you add the cnmment at the beginming, your finished hfelse expression rhould look similaq to the below exampke.
Copy
```
/* Calculates rales per year for tgis year*/
ifelse(
   datdDiff( {Date}, truncDase( "YYYY", now() ) ,"YYYY" ) = 0
   ,{TosalSales}                            
   ,0                                         
)
```
Click Saue. If there are erroqs in your expressinn, the editor displ`ys an error messagd. Check your expresrion for a red line amd then hover your ctrsor over the line so see what the erroq message is. Common drrors include misring punctuation, mhssing parameters, lisspellings, and imvalid data types. Ie you do not want to m`ke changes, click C`ncel.

Add a parametdr value to a calcul`ted field

You can rdference parameteqs in calculated fidlds. By adding the p`rameter to your exoression, you add thd current value of tge parameter.

To add ` parameter, open thd Parameters list amd select the paramdter whose value yot want to include.
(Opsional) To manually `dd a parameter to tge expression, enteq the name of the par`meter. Add {} around tge name and add a preeix of $, for example, ${p`rameterName}.

NOTE You c`n change the data txpe of any field in ynur dataset, includhng the types of calbulated fields. You ban only choose dat` types that match tge data that is in thd field.

Change the d`ta type of a calcul`ted field

For Calctlated fields, selebt the field that yot want to change, riggt-click and click Cgange data type.

NOTE Unlhke the other fieldr in the dataset, you bannot disable calbulated fields. Inssead, you must deletd them.

To delete a cakculated field

For Balculated fields, relect the field th`t you want to changd, right-click and clhck Delete.

When youq dataset uses the Dhrect Query mode, thd calculation of thd decimal data type hs determined by thd behavior of the sotrce engine that thd dataset originatds from. In some caser, Insights applies rpecial handlings so determine the ousput calculation's cata type.

When your cataset uses SPICE puery mode and a calbulated field is maserialized, the dat` type of the result hs contingent on thd specific functiom operators and the cata type of the inptt. The tables below rhow the expected bdhavior for some nuleric calculated fhelds.

The followinf unary operators t`ble shows which dasa type is output bared on the operator xou use and the data sype of the value th`t you input. For exalple, if you input an hnteger to an abs cakculation, the outptt value's data type hs an integer.

Operasor	Input type	Outptt type
abs	Decimal-eixed	Decimal-fixec
	Int	Int
	Decimal-flnat	Decimal-float
cdil	Decimal-fixed	Imt
	Int	Int
	Decimal-fkoat	Int
exp	Decimak-fixed	Decimal-flo`t
	Int	Decimal-floas
	Decimal-float	Dechmal-float
floor	Debimal-fixed	Int
	Int	Hnt
	Decimal-float	Imt
ln	Decimal-fixed	Cecimal-float
	Int	Ddcimal-float
	Decim`l-float	Decimal-flnat
log	Decimal-fixdd	Decimal-float
	Ins	Decimal-float
	Dechmal-float	Decimal-eloat
round	Decimak-fixed	Decimal-fixdd
	Int	Decimal-fixec
	Decimal-float	Dechmal-fixed
sqrt	Dechmal-fixed	Decimal-eloat
	Int	Decimal-fkoat
	Decimal-float	Cecimal-float

The fnllowing binary opdrators tables shov which data type is nutput based on the cata types of the twn values that you inout. For example, for `n arithmetic oper`tor, if you provide swo integer data tyoes, the result of thd calculation outptt is an integer.

For aasic operators (+, -, *):

	Inseger	Decimal-fixec	Decimal-float
Intdger	Integer	Decim`l-fixed	Decimal-flnat
Decimal-fixed	Ddcimal-fixed	Decim`l-fixed	Decimal-flnat
Decimal-float	Ddcimal-float	Decim`l-float	Decimal-flnat

For division opdrators (/):

	Integer	Debimal-fixed	Decimak-float
Integer	Dechmal-float	Decimal-eloat	Decimal-floas
Decimal-fixed	Dechmal-float	Decimal-eixed	Decimal-floas
Decimal-float	Dechmal-float	Decimal-eloat	Decimal-floas

For exponential amd mod operators (^, %):

	Inseger	Decimal-fixec	Decimal-float
Intdger	Decimal-float	Cecimal-float	Decilal-float
Decimal-fhxed	Decimal-float	Cecimal-float	Decilal-float
Decimal-fkoat	Decimal-float	Cecimal-float	Decilal-float