8,981
edits
Changes
no edit summary
To create the expression (formula) in the components, the component must support the addition of the special column ''Expression (Formula)''.
In TaticView , all columns in the data file can be used in expressions as long as they are also added to the component that will receive the expression.
In addition, it is also possible to use fixed numbers and texts in the case of comparative expressions.
Expression Example:
* If the component has the columns ''Total Price'' and ''Quantity'', you can create an expression to calculate the ''Unit Price''. Would be like this:
** ''Total Price'' / ''Quantity''
* In addition, you can use fixed values, such as:
** ''Total price'' / 10
We just remember that currently TaticView only supports expressions whose numeric return.
<operating or expression result> '''+''' <operating or expression result>
Example:
'''"Taxes" + "Commissions"''' - Sums the values of the fields ''Taxes'' and ''Commissions''
<operating or expression result> '''-''' <operating or expression result>
Example:
'''"Price" - "Taxes"''' - Subtracts the values from the ''Price'' and ''Taxes'' fields.
<operating or expression result> '''*''' <operating or expression result>
Example:
'''"Quantity" * "Unit Price"''' - Multiplies the value of the ''Quantity'' field by the value of the ''Unit Price'' field.
<operating or expression result> '''/''' <operating or expression result>
Example:
'''"Price" / "Quantity"''' - Divides the value of the ''Price'' field by the value of the ''Quantity'' field.
<operating or expression result> '''==''' <operating or expression result>
Example:
'''IF("Product" == "#null";0;20)''' - If the ''Product'' is null, return the value ''0''. Otherwise return the value ''20''
<operating or expression result> '''!=''' <operating or expression result>
Example:
'''IF("Product" != 'Lasagna';0;20)''' - If the ''Product'' is different from ''Lasagna'', return the value ''0''. Otherwise return the value ''20''
<operating or expression result> '''>''' <operating or expression result>
Example:
'''IF("Gross Value" > 1000;1;0)''' - If the ''Gross Value'' is greater than ''1000'', returns the value ''1'', if not the value ''0''
<operating or expression result> '''>=''' <operating or expression result>
Example:
'''IF("Gross Value" >= 2500;100;0)''' - If ''Gross Value'' is greater than or equal to ''2500'' returns ''100'', otherwise returns ''0''
<operating or expression result> '''<''' <operating or expression result>
Example:
'''IF("Gross Value" < 100;50;200)''' - If ''Gross Value'' is less than ''100'', returns ''50'' if not ''200''
<operating or expression result> '''<=''' <operating or expression result>
Example:
'''IF("Gross Value" <= 500;100;0)''' - If ''Gross Value'' is less than or equal to ''500'' returns ''100'', if not ''0''
<expression> && <expression>
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''
<expression> || <expression>
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'' '''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''
!<expression>
Example:
'''IF(!BETWEEN("Delivery Date"; '01/01/2015'; '31/12/2015');2016;2015)''' - If the ''Delivery Date'' is '''NOT''' between ''01/01/2015'' and ''12/31/2015'' returns ''2016''. If between this period returns ''2015''
ABS(<value operator or expression>)
Example:
'''ABS("Price" / "Quantity")''' - Returns the absolute value of ''Price'' divided by ''Quantity''
AVG(<expression 1>; <expression 2>; ...)
Example:
'''AVG("Taxes"; "Profit"; "Cost")''' - Returns the average between the sum of the ''Taxes'', ''Profit'' and ''Cost'' columns
BETWEEN(<expression to be tested>; <initial limit>; <end limit>)
Example:
'''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
IF(<expression to be tested>; <expression if true>; <expression if false>)
Examples:
'''IF(BETWEEN("Value";0;100);10;250)''' - If ''Value'' is between ''0'' and ''100'' returns ''10'', if not ''250'' '''IF("Profit" == 0;0;"Profit" + "Taxes")''' - If ''Profit'' equals ''0'' returns ''0'', if it does not return the sum between ''Profit'' and ''Taxes''
LOG(<expression>;<base>)
Examples:
'''LOG("Profit")'''- Returns the logarithm of the field ''Profit'' '''LOG("Price";2)''' - Returns the logarithm of the ''Price'' field in base ''2''
MAX(<expression 1>;<expression 2>)
Example:
MIN(<expression 1>; <expression 2>)
Example:
'''"Value" / MIN("Cost"; 100)''' - Divides ''Value'' by the smallest value between ''Cost'' and ''100''
POW(<expression>; <exponent>)
Examples:
'''POW("Profit")''' - Returns the power of the ''Profit'' value. '''POW("Cost"; 3)''' - Returns the power of the ''Cost'' value, raised to ''3''
RANDOM(<start limit>; <end limit>)
Example:
'''RANDOM(10,100)''' - Returns a random number between ''10'' and ''100''
SQRT(<value or expression>)
Examples:
'''SQRT("Profit")''' - Returns the square root of the value ''Profit'' '''SQRT(81)''' - Returns the square root of ''81''
LIKE(< data>; <term>)
Example:
'''LKE("Product"; '%anh%')''' - Returns true if the ''Product'' contains the term ''anh'' or false otherwise
DATE_ PART(<date>; <constant representing which part>)
Example:
'''DATE_ PART("Issued Date"; 0) ''' - Returns the year portion of the ''Issued Date''
DAY_FROM_TODAY(<number of days>)
Example:
'''DAY_FROM_TODAY(-1) ''' - Returns yesterday's date
FIRST_DAY_OF(<date part>; <base date>)
Examples:
'''FIRST_DAY_OF(0;DAY_FROM_TODAY(0)) ''' - Returns the first day of the year from today '''FIRST_DAY_OF(1;"Issue Date") ''' - First Day of Month Based on ''Issue Date'' Column
* With three parameters, the first is the grouping constant from which we want to get the first date (see list above), the second is the date base modifier from today, and the third is the amount to change the modifier.
FIRST_DAY_OF(<date part>;<date modifier>;<date modifier value>)
Example:
'''FIRST_DAY_OF(1,0,-1) ''' - first day of current month of last year
LAST_DAY_OF(<date part>; <base date>)
Examples:
'''LAST_DAY_OF(0;DAY_FROM_TODAY(0)) ''' - Returns the last day of the year from today. '''LAST_DAY_OF(1;"Date Issued") ''' - Last day of month based on ''Date Issued'' column
* With three parameters, the first is the grouping constant from which we want the last date (see list above), the second is the date base modifier from today, and the third is the amount to change the modifier.
LAST_DAY_OF(<date part>;<date modifier>;<date modifier value>)
Example:
'''LAST_DAY_OF(1,0,-1) ''' - last day of current month of last year
NEW_DATE(<day>;<month>;<year>)
Example:
'''NEW_DATE(1;4;DATE_PART("Issued Date";0)) ''' - Creates the first April day of the year determined by the current row in the ''Issued Date'' column.
NUM_OF_DAYS(<date 1>; <date 2>)
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. '''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.
NUM_OF_MONTHS(<date 1>; <date 2>)
Example:
'''NUM_OF_MONTHS(FIRST_DAY_OF(0; "Date"); "Date") ''' - Returns the number of months between the first day of the year and the current month, based on the ''Date'' column.
PREVIOUS_DATE(<day>; <month>)
Examples:
'''PREVIOUS_DATE(15;12) ''' - Considering that today's date is 14/11/2019, the last time December 15th occurred relative to today's date was 15/12/2018, so this will be the return.