Open main menu

TaticView β

Changes

Expressions

1,512 bytes added, 02:26, 5 October 2022
Marked this version for translation
<!--T:69-->
You can create expressions (formulas) directly in the dashboard components. It can thus display calculated values that are not present in the data source datasource file/spreadsheet.
We just remember that currently TaticView only supports expressions whose numeric return.
 
<!--T:225-->
Expressions support the backslash as [[EscapeChar|escape character]] so that names can contain the delimiting characters.
<!--T:76-->
== Basic structure of a Expression (Formula) == <!--T:77-->
==== Identifier ==== <!--T:226-->
 
<!--T:227-->
It is the name of the field or column involved in the expression, it must be delimited by double quotes.
 
<!--T:228-->
* Examples: "Average Price". "Screw 1\" philips", "Seler"
==== Value Operand ==== <!--T:78-->
* Examples: 1, 1.05, 2.55
<!--T:220-->
* Important: Do not use thousand separator in numeric values.
==== Text Operand ==== <!--T:81-->
<!--T:95-->
* Example: IF("Product" == "#null";0;20)
 
== Arithmetic Expressions == <!--T:96-->
Examples:
'''IF(("Price" > 0) && ("Seller" == 'John Snow');450;0)''' - If ''Price'' is greater than ''0'' '''AND''' ''Seller'' is equal to ''John Snow'', returns ''450'' if not returns ''0''
'''IF(("City" != "#null") && ("Product" != 'Lasagna');1;0)''' - If ''City'' is non-null '''AND''' ''Product'' is different from ''Lasagna'' returns ''1'' if it does not return ''0''
Examples:
'''IF(("Price" > 1000) || ("Profit" >= 50);75;55)''' - If ''Price'' is greater than ''1000'' '''OR''' ''Profit'' is greater than or equal to ''50'' returns ''75'' if not ''55''
  <!--T:138-->'''IF(("City" != "#null") || ("Product" != 'Lasagna');1;0)''' - If ''City'' is non-null '''OR''' ''Product'' is different from ''Lasagna'' returns ''1'' if it does not return ''0''
'''BETWEEN("Date Issued"; '01/01/2019'; '31/12/2019')''' - Returns true if ''Date Issued'' is between ''01/01/2019'' and ''12/31/2019''. Otherwise returns false
==== COUNT function ==== <!--T:229-->
 
<!--T:230-->
Returns number of occurrences (lines) for column or value.
 
<!--T:231-->
By now this functions works only at Datasource expressions
 
<!--T:232-->
Syntax:
COUNT(<expression>)
Example:
'''COUNT("Product")''' - Returns number of occurrences (lines) of products
 
==== COUNT_DISTINCT function ==== <!--T:233-->
 
<!--T:234-->
Returns number of unique occurrences (lines) for column or value.
 
<!--T:235-->
By now this functions works only at Datasource expressions
 
<!--T:236-->
Syntax:
COUNT_DISTINCT(<expression>)
Example:
'''COUNT_DISTINCT("Product")''' - Returns number of unique (different) occurrences (lines) of products
==== IF ELSE function (If .... Otherwise ...) ==== <!--T:153-->
Examples:
'''IF(BETWEEN("Value";0;100);10;250)''' - If ''Value'' is between ''0'' and ''100'' returns ''10'', if not ''250''
  <!--T:156-->'''IF("Profit" == 0;0;"Profit" + "Taxes")''' - If ''Profit'' equals ''0'' returns ''0'', if it does not return the sum between ''Profit'' and ''Taxes'' '''IF("Product Code" == 3231;0;"Quantity" * 3;"Quantity")''' - If ''Product Code'' equals ''3231'' returns ''Quantity'' plus 3
==== LOG function ==== <!--T:157-->
<!--T:158-->
Returns the logarithm of a number on a defined basis. It can be used with only one parameter, in this case the neperian (natural) "base 10" logarithm is used . If two parameters are used, the second server to inform the base to be used by the logarithm.
<!--T:159-->
Examples:
'''LOG("Profit")'''- Returns the logarithm of the field ''Profit''
  <!--T:160-->'''LOG("Price";2)''' - Returns the logarithm of the ''Price'' field in base ''2''
Examples:
'''POW("Profit")''' - Returns the power of the ''Profit'' value.
  <!--T:170-->'''POW("Cost"; 3)''' - Returns the power of the ''Cost'' value, raised to ''3''
'''RANDOM(10;100)''' - Returns a random number between ''10'' and ''100''
==== ROUND function ==== <!--T:222-->
 
<!--T:223-->
Returns the rounded value of a value column or value.
 
<!--T:224-->
Syntax:
ROUND(<value>;<precision>)
Example:
'''ROUND("Profit")''' - If Profit 10.38 returns 10.00
'''ROUND(15.65)''' - Returns 16.00
'''ROUND(1.777;1)''' - Returns 1.8
==== SQRT function (Square Root) ==== <!--T:174-->
Examples:
'''SQRT("Profit")''' - Returns the square root of the value ''Profit''
  <!--T:177-->'''SQRT(81)''' - Returns the square root of ''81''
LIKE(< data>; <term>)
Example:
'''LKELIKE("Product"; '%anh%')''' - Returns true if the ''Product'' contains the term ''anh'' or false otherwise 
== Date Functions == <!--T:181-->
Examples:
'''FIRST_DAY_OF(0;DAY_FROM_TODAY(0))''' - Returns the first day of the year from today
  <!--T:194-->'''FIRST_DAY_OF(1;"Issue Date")''' - First Day of Month Based on ''Issue Date'' Column
<!--T:195-->
Examples:
'''LAST_DAY_OF(0;DAY_FROM_TODAY(0))''' - Returns the last day of the year from today.
  <!--T:201-->'''LAST_DAY_OF(1;"Date Issued")''' - Last day of month based on ''Date Issued'' column
<!--T:202-->
Examples:
'''NUM_OF_DAYS(FIRST_DAY_OF(0;"Issued Date");"Issued Date")''' - Returns the number of days from the first day of the year, based on the ''Issued Date'' column, to the current day, based on the same column.
  <!--T:210-->'''NUM_OF_DAYS(FIRST_DAY_OF(1;"Issued Date");LAST_DAY_OF(1;"Issued Date"))''' - Returns the number of days of the month, based on the ''Issued Date'' column.
* '''"@avg[column]"''' - Average column, similar to ''"@sum[column]"'' / ''"@count"''
* '''"@prev[column]"''' - Row value before current row for this column
* '''"@totalRowValue[column]"''' - Value of total row, (if present), from this column.
* '''"@accumCol[column]"''' - Cumulative value from column to current row. Useful for calculating variable mean.
** Example: ''"@accumCol[column]"'' / ''"@row"''
</translate>
Retrieved from "http://wiki.taticview.com/index.php/Special:MobileDiff/27635...29810"