KURVE CORE

[Pages:25]Updated as of March 18, 2021

KURVE CORE

Custom Column

User Guide

1

Table of Contents

Basic Expressions.............................................................................................................4

Number ........................................................................................................................................4 Make a percentage ............................................................................................................................................ 4 Add two columns together ................................................................................................................................ 5

Text ..............................................................................................................................................5 Check One Condition ? true/false output ....................................................................................................... 5 Adding Two Columns Together ....................................................................................................................... 5

Date ..............................................................................................................................................6

Colour ..........................................................................................................................................6

Functions ............................................................................................................................ 7

TRIRIGA Functions ....................................................................................................................7 Get Path Level.................................................................................................................................................... 7 Is Blank ................................................................................................................................................................ 7

Date Functions ...........................................................................................................................7 Add Days............................................................................................................................................................. 7 Add Time ............................................................................................................................................................. 8 Add TRIRIGA Duration ..................................................................................................................................... 8 Convert Duration ................................................................................................................................................ 8 Convert Milliseconds ......................................................................................................................................... 8 Convert TRIRIGA Duration............................................................................................................................... 9 Days Between .................................................................................................................................................... 9 FilterDay(date, range) ....................................................................................................................................... 9 FilterMonth(date, range) ................................................................................................................................... 9 FilterYear(date, range) .................................................................................................................................... 10 Format Date ...................................................................................................................................................... 10 Get Day ............................................................................................................................................................. 10 Get Day of Week.............................................................................................................................................. 11 Get Day of Year ............................................................................................................................................... 11 Get End of Month............................................................................................................................................. 11 Get End of Year ............................................................................................................................................... 11 Get First Of Month ........................................................................................................................................... 11 Get First Of Year .............................................................................................................................................. 11 Get Month ......................................................................................................................................................... 12 Get Month Name.............................................................................................................................................. 12 Get Quarter ....................................................................................................................................................... 12 Get Week .......................................................................................................................................................... 12 Get Weeks in a Month..................................................................................................................................... 13 Get Year ............................................................................................................................................................ 13 Is Date After ...................................................................................................................................................... 13 Is Date Before................................................................................................................................................... 13 Is Date Between ............................................................................................................................................... 14 Now .................................................................................................................................................................... 14 Subtract TRIRIGA Duration............................................................................................................................ 14 Time Between................................................................................................................................................... 14 Today ................................................................................................................................................................. 15

Number Functions ...................................................................................................................16

2

Format Currency .............................................................................................................................................. 16 Maximum ........................................................................................................................................................... 16 Minimum ............................................................................................................................................................ 16 Round a Number.............................................................................................................................................. 16 Text Functions .........................................................................................................................17 Delimiter/Splitter ............................................................................................................................................... 17 Group Nulls ....................................................................................................................................................... 17 PadEnd(input, length, fill) ............................................................................................................................... 17 PadStart(input, length, fill) .............................................................................................................................. 17 Truncate ............................................................................................................................................................ 18

IF statements....................................................................................................................19 Text ............................................................................................................................................19

Text output ? 2 conditions .............................................................................................................................. 19 Text output ? 3+ conditions ............................................................................................................................ 19 Number ...................................................................................................................................... 20 Numerical output ? 2 conditions .................................................................................................................... 20 Date ............................................................................................................................................ 20 Date output ? 2 conditions.............................................................................................................................. 20 Color ..........................................................................................................................................21 Color ? 2 conditions......................................................................................................................................... 21 Color ? 3+ conditions ...................................................................................................................................... 22 Color & Text..............................................................................................................................23 Color Column Snippet ..................................................................................................................................... 23 Color Column Snippet: Referencing Other Column as Output................................................................. 23 OR Statements.............................................................................................................................24 Color Picker (Introduced in Version 1.12) ......................................................................................24

3

Now that you know how to create a custom column as explained in our Kurve Core user guide and how-to video, this document will provide examples of expressions to help you customize your report and make the most out of custom columns.

Column Type

Description

Text Column

Text columns should be used for alphabetic characters. For

example, if you are creating a column to display months, it would

be a text column displaying months January through December

based on the source column.

A text column will filter for unique values.

Number Column A number column would be used for columns that will result in

numeric characters. For example, if you are calculating a

percentage you would use a number column.

Using a number column will allow you to filter by greater than, less

than, equal to, not equal to, etc.

Date Column

A date column will be used for all custom columns involving

dates. For example, if you are adding a column to show Today's

Date, the column type would be Date.

A date column filter will provide a calendar for you to choose what

you would like to filter for.

Date/Time Column A date/time column will be used for all custom columns involving

dates in which the source column has the time of day as well. For

example, 10/09/2018 12:01:47.

A date/time column filter will provide a calendar and time

selection for you to choose what you would like to filter for.

Colour Column Colour columns are used for colour coding specific columns.

Any new column can be colored.

Basic Expressions

Number

Make a percentage If I have a column that is already a decimal, I could add that column into the expression tab and then multiply by 100.

numCol('decimal')*100

If you want to find the percentage change between two numbers:

(numCol('Score')/numCol('Total'))*100

4

Add two columns together To add two columns together, I would select the first column from my source column, add it as a number, click or type the plus sign, and then select the second column I would like to add. For example, to get a total number of parking spaces in the example below:

numCol('Parking Spaces (Open)')+numCol('Parking Spaces (Covered)')

Text Check One Condition ? true/false output This would be used to determine if something is true or false according to your parameter. The output of the following expression will be true if the value for that row in the State/Province column is New York. If it isn't New York, the output will be false. Don't forget the double equal sign!

col('State/Province')=="New York" This also works for checking if something is greater than or less than! The example below will return true if the number is greater than 10,000. When adding the source column into the expression tab, add it as a number to ensure it is the right format.

numCol('Site Gross Area')>10000 This works for checking dates as well.

dateCol('Must Exercise Option By')>Today()

Adding Two Columns Together If you would like to combine two text columns you can easily do so in the expression tab. Let's say we want City and State in the same column, separated by a comma.

col("City") +", " +col("State/Province")

5

Date We can also output a date. The best ways to do this are outlined in the functions section. Colour A color column can be created with either just one color or multiple colors according to certain conditions. To output just one column, in the expression tab, put the color or hex code in quotations:

"Red" To output multiple colors you would use an if statement, this will be covered in the section regarding IF statements. Use the color picker within the operators to easily add a color into the expression box.

6

Functions

TRIRIGA Functions

Get Path Level Data Type: Text This function is used to retrieve a specific level from a TRIRIGA hierarchy path field. For example, if you have a hierarchy path for Space and you want to drill down to the fourth level I would add my Source column to replace path and then indicate the level with a numerical value:

GetPathLevel(path, level) GetPathLevel(col('Hierarchy Path'), 4)

Is Blank Data Type: Text Sometimes the formatting of blanks gets confusing in TRIRIGA. This function returns true if the value is either " " or null, otherwise, it will return false.

IsBlank(value) IsBlank(col('City'))

Date Functions

Important Note: When outputting a date column, we recommend doing all of the operations within one custom column (including re-formatting and any use of functions). We don't recommend users chain reference date columns in multiple custom columns. If you do need to chain them, please avoid changing the output date format within the process, and only use FormatDate function at very end of the chaining operations. We can combine multiple functions within one column.

For example, if you would like to add time as well as reformat a date we can nest the functions together like the following expression:

FormatDate(AddTime(dateCol("Project Plan End"), 2, "seconds"),"yyyy-MM-dd hh:mm:ss")

Add Days Data Type: Date or Date/Time Adds a specified number of days to the given date. Can also be used to subtract days. For example, if you want to add a buffer period to an expiration date you can use this

7

function. The example below will return the date in 14 days from the Contract Expiration Date.

AddDays(date, numberToAdd) AddDays(dateCol('Contract Expiration Date'), 14)

Add Time Data Type: Date or Date/Time Adds a specified number of a given unit of time to the given date. For example, you can add hours, days, weeks, months, etc. The following adds two weeks to the Contract Expiration Date.

AddTime(date, numberToAdd, unit) AddTime(dateCol('Contract Expiration Date'), 2, "weeks")

Add TRIRIGA Duration Data Type: Number Adds a TRIRIGA duration to a given date and returns the resulting date. For example, if you have a field Due Within that is a duration field and a start date of works tasks, you can use this function to determine when the task is supposed to be due.

AddTRIDuration(date, duration) AddTRIDuration(dateCol('Start Date'), col('Due Within'))

Convert Duration Data Type: Number Converts a given duration from one unit of time to another. Supports milliseconds, seconds, minutes, hours, days, weeks, months, and years. The following expressions will convert the column Days Between from the number of days to the number of weeks.

ConvertDuration(duration, input, output) ConvertDuration(numCol('Days Between'), "days", "weeks")

Convert Milliseconds Data Type: Number TRIRIGA uses Epoch time to manage their dates so sometimes your output in a custom column may provide milliseconds. This function will format a given duration into another

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download