Difference between revisions of "Excel to Analytica Mappings/Database Functions"

Latest revision as of 00:02, 16 March 2016

This page shows Analytica expressions that are equivalent to selected Excel database functions. They assume that you are applying each Excel function to a range, named relational table, t.

In Analytica, you apply each function to an Array, t, indexed by Rows and Cols. Index Cols identifies the columns or fields of each record. criteria is a Boolean expression involving t[Cols == field] that evaluates to 0 or 1 for each Row.

DAVERAGE(t, field, criteria)

Analytica equivalent:

Mean(t[Cols == field], Rows, w: criteria)

DCOUNT(t, field, criteria)

Analytica equivalent:

Sum(criteria, Rows)

DGET(t, field, criteria)

Analytica equivalent:

t[Cols == field, Rows == SubIndex(criteria, 1, Rows)]

DMAX(t, field, criteria)

Analytica equivalent:

CondMax(t[Cols == field], criteria, Rows)

DMIN

Analytica equivalent:

CondMin(t[Cols == field], criteria, Rows)

DPRODUCT(t, field, criteria)

Analytica equivalent:

Product(IF criteria THEN t[Cols ==field] Else 1, Rows)

DSTDEV(t, field, criteria)

Analytica equivalent:

SDeviation(t[Cols ==field], Rows, w: criteria)

DSUM(g, field, criteria)

Analytica equivalent:

Sum(If criteria Then t[Cols == field] Else 0, Rows)

DVAR

Analytica equivalent:

Variance(t[Cols == field], Rows, w: criteria)

@@ Line 1: / Line 1: @@
-This page shows how selected database functions in Excel map to Analytica equivalents.
+[[Category:Excel to Analytica mappings]]
-= DAVERAGE(table,field'',criteria'') =
+__TOC__
-Analytica equivalent:
- [[Mean]]( table[FieldIndex=field], RowIndex'', w:criteria'' )
-= DCOUNT(table,field,criteria) =
+This page shows Analytica expressions that are equivalent to selected Excel database functions.
+They assume that you are applying each Excel function to a range,  named relational table,  <code>t</code>.
-Analytica equivalents:
+In Analytica,  you apply each function to an Array,  <code>t</code>,  indexed by <code>Rows</code> and <code>Cols</code>.
- [[Sum]]( if criteria and IsNumber(table[FieldIndex=field]) else 0, RowIndex )
+Index <code>Cols</code> identifies the columns or fields of each record.
- [[Sum]]( criteria, RowIndex )
+criteria is a Boolean expression involving <code>t[Cols == field]</code> that evaluates to 0 or 1 for each Row.
-where criteria is a boolean comparison that evaluates to 0 or 1.
+== DAVERAGE(t,  field,  criteria) ==
-= DGET(table,field,criteria) =
+''Analytica equivalent: ''
+:[[Mean]](t[Cols == field],  Rows,  w<nowiki>:</nowiki> criteria)
-Analytica equivalent:
+== DCOUNT(t,  field,  criteria)  ==
- table[FieldIndex=field,RowIndex=[[SubIndex]](criteria,1,RowIndex)]
-= DMAX(table,field,criteria) =
+''Analytica equivalent: ''
+:[[Sum]](criteria,  Rows)
-Analytica equivalent:
+== DGET(t,  field,  criteria) ==
-  [[CondMax]](table[fieldIndex=field],criteria,RowIndex)
-= DMIN =
+''Analytica equivalent: ''
-Analytica equivalent:
+:t[Cols == field,  Rows == [[SubIndex]](criteria,  1,  Rows)]
-  [[CondMin]](table[fieldIndex=field],criteria,RowIndex)
-= DPRODUCT(table,field,criteria) =
+== DMAX(t,  field,  criteria) ==
-Analytica equivalent:
+''Analytica equivalent:''
- [[Product]]( If criteria Then table[FieldIndex=field] Else 1, RowIndex )
+:[[CondMax]](t[Cols == field],  criteria,  Rows)
-= DSTDEV(table,field,criteria) =
+== DMIN ==
+''Analytica equivalent:''
+:[[CondMin]](t[Cols == field],  criteria,  Rows)
-Analytica equivalent:
+== DPRODUCT(t,  field,  criteria) ==
-  [[SDeviation]]( table[FieldIndex=field], RowIndex, w:criteria )
-= DSUM(table,field,criteria) =
+''Analytica equivalent:''
+:[[Product]](IF criteria THEN t[Cols ==field] Else 1,  Rows)
-Analytica equivalent:
+== DSTDEV(t,  field,  criteria) ==
-  [[Sum]]( If criteria Then table[FieldIndex=field] Else 0, RowIndex )
-= DVAR =
+''Analytica equivalent:''
+:[[SDeviation]](t[Cols ==field],  Rows,  w<nowiki>:</nowiki> criteria)
-Analytica equivalent:
+== DSUM(g,  field,  criteria) ==
-  [[Variance]]( table[FieldIndex=field], RowIndex, w:criteria )
+''Analytica equivalent:''
+:[[Sum]](If criteria Then t[Cols == field] Else 0,  Rows)
+== DVAR ==
+''Analytica equivalent:''
+:[[Variance]](t[Cols == field],  Rows,  w<nowiki>:</nowiki> criteria)
+==See Also==
+* [[Excel to Analytica Mappings]]