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

Revision as of 20:45, 22 February 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 2: / Line 2: @@
 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.
+They assume that you are applying each Excel function to a range,  named relational table,  <code>t</code>.
-In Analytica,  you apply each function to an Array,  <code>t</code>,  indexed by Rows and Cols.
+In Analytica,  you apply each function to an Array,  <code>t</code>,  indexed by <code>Rows</code> and <code>Cols</code>.
 Index <code>Cols</code> 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.
+criteria is a Boolean expression involving <code>t[Cols == field]</code> that evaluates to 0 or 1 for each Row.
 == DAVERAGE(t,  field,  criteria) ==
 Analytica equivalent:
- Mean(t[Cols == field],  Rows,  w: criteria)
+:[[Mean]](t[Cols == field],  Rows,  w: criteria)
 == DCOUNT(t,  field,  criteria)  ==
 Analytica equivalent:
- Sum(criteria,  Rows)
+:[[Sum]](criteria,  Rows)
 == DGET(t,  field,  criteria) ==
 Analytica equivalent:
- t[Cols == field,  Rows == SubIndex(criteria,  1,  Rows)]
+:t[Cols == field,  Rows == [[SubIndex]](criteria,  1,  Rows)]
 == DMAX(t,  field,  criteria) ==
 Analytica equivalent:
- CondMax(t[Cols == field],  criteria,  Rows)
+:[[CondMax]](t[Cols == field],  criteria,  Rows)
 == DMIN ==
 Analytica equivalent:
- CondMin(t[Cols == field],  criteria,  Rows)
+:[[CondMin]](t[Cols == field],  criteria,  Rows)
 == DPRODUCT(t,  field,  criteria) ==
 Analytica equivalent:
- Product(IF criteria THEN t[Cols ==field] Else 1,  Rows)
+:[[Product]](IF criteria THEN t[Cols ==field] Else 1,  Rows)
 == DSTDEV(t,  field,  criteria) ==
 Analytica equivalent:
- SDeviation(t[Cols ==field],  Rows,  w: criteria)
+:[[SDeviation]](t[Cols ==field],  Rows,  w: criteria)
 == DSUM(g,  field,  criteria) ==
 Analytica equivalent:
- Sum(If criteria Then t[Cols == field] Else 0,  Rows)
+:[[Sum]](If criteria Then t[Cols == field] Else 0,  Rows)
 == DVAR ==
 Analytica equivalent:
- Variance(t[Cols == field],  Rows,  w: criteria )
+:[[Variance]](t[Cols == field],  Rows,  w: criteria)
+==See Also==
+* [[Excel to Analytica Mappings]]