Formula Engine

How can I get the last item in a string of comma separated values? #

This formula returns a comma separated list of sprint names:

REGEX(‘1- Resolution filter’.Sprint, NULL, “(?<=name=).*"), NULL, "^([^,])+")

e.g. SprintName 1, SprintName 2, SprintName 3

We will use 2 formulas to extract the last item i.e. Sprint 3 in the given case.

Formula 1:

Name: Concat Sprints value
Logic: CONCAT(CONCATENATE(Issues.’Sprint Names’, “”, “”, “,”))

Formula 2:
Name: Last Item(Sprint 3)

Logic: RIGHT(Issues.’Concat Sprints value’, (LENGTH(Issues.’Concat Sprints value’)-SEARCH(“,”, Issues.’Concat Sprints value’, 1, 1, “end”)))

Does anyone have an example of how to use the holidays parameter in the NETWORKDAYS formula? The tooltip says that you have to use a range containing the dates so I was wondering if someone could share some working examples. #

We do have formula Network days, there are 4 parameters we must consider while creating Network days formula.

1. Start Date
2. End Date
3. Date Range for Holidays.
   • You must provide the column wherein you have all the non-working days
4. Define weekdays and weekend.
   • It starts from Monday and ends on Sunday.
   • Working days are defined with ‘0’. It implies 5 zeros from Monday-Friday
   • Two ‘1’s’ for Saturday Sunday.

In the below example,

There are 22 working days in month of September-2021 whereas we have opted for 2 non-working days i.e. 21-22 September.

Therefore, with the given formula logic. It returns 20 working days for the month of September.

How can I convert a string ’25/Jan/21′ to a Datevalue? #

Please use below formula to convert string 25/Jan/21 into a date value.

DATEVALUE(CONCAT(LEFT(‘column_name’, (SEARCH(“/”, ‘column_name’, 1, 1, “start”)-1)), “-“, REGEX(‘column_name’, NULL, “(?<=/)(.*?)(?=/)"), "-", RIGHT('column_name', (SEARCH("/", 'column_name', 1, 1, "start")-1))))

What are commonly used Formulas while working on Gathr Analytics? #

We have list of formulas in our Gathr Analytics learning center. https://support.klera.io/hc/en-us/articles/360034515353-Formulas-Add-Computed-Columns This will help you get started with formulas on Gathr Analytics. Formula is growing more with numbers, as we keep on adding them regularly.

How to view all the Formulas applied on a Dataset? #

• Click on execution graph. Check formula option on top left corner.
• You will find formula node in green color associated to a dataset.
• Click on formula node, and you will see the list of formulas in information panel. Please refer the image in attachment.
• You can also click on container, and click on visualization tab.
• If you check the dataset, you fill 2 sections: Formula and Data.
• Under the formula section, you fill find all the formulas applied on the dataset.

Which EPOCH format is supported in application builder? #

Application builder supports EPOCH Timestamp in milliseconds i.e. it converts the EPOCH value(in milliseconds) received from an API to a human readable DateTime format. e.g. EPOCH value ‘1621878464000’ will be converted to ’24-May-2021 23:17:44 IST’ DateTime format.

If the EPOCH value received from an API is in seconds, then first we need to convert it into milliseconds by multiply it by 1000 and then it can be converted to DateTime format using DateTimeValue formula on Gathr Analytics.

How to add a package while registering a Python script in Gathr Analytics? #

To register a Python script as Gathr Analytics Operation

• Right-click on a container–>Scripts–>Register

It will open a pop-up window, please fill in the details as below

• Operation Name: Name of the operation to be exposed on Gathr Analytics
• Hierarchy to guide user (Level1/Level2): Hierarchy of the operation that would be created and visible to users
• Script: Browse to the script file to be registered as operation

If your script requires any Python package, then it can be installed by adding package name and version in the ‘Packages’ tab as shown below.

If the version is not provided, then the latest version would be installed. Installing the python package will require an active internet connection as it will download the aforementioned package from public repositories (PIP).

• Click submit to register the script.

To list out the registered Python scripts, Right-click on floor–>Scripts–>Show.

How to calculate working hours between 2 dates in Gathr Analytics? #

You can calculate the working hours between 2 dates using the series of formulas mentioned below. These formulas can easily be edited to add more conditions and can also be merged into one formula or group of formulas.

Important:

1. You can easily modify these formulas to accommodate more conditions and cases.
2. You can use Python script-based Operation or Formula directly on Gathr Analytics to solve complex use cases.

FORMULAS:

1. [(Column Name) – Start Date Only] | [(Description) – find the date of start datetime] | [(Formula Syntax) – DATETIMEVALUE(CONCAT(DAY(‘User Templates’.’Start DateTime’), “-“, MONTH(‘User Templates’.’Start DateTime’), “-“, YEAR(‘User Templates’.’Start DateTime’)), “dd-MM-yyyy”, ,”datetime”, “GMT”)]
2. [(Column Name) – Start Date Hours] | [(Description) – Find the hours in the start datetime] | [(Formula Syntax) – DATEDIFF(‘User Templates’.’1 Start Date Only’, ‘User Templates’.’Start DateTime’, “MINUTE”)/60]
3. [(Column Name) – Downtime in Start Date] | [(Description) – Find the downtime in start datetime] | [(Formula Syntax) – IF(OR(‘User Templates’.’2 Start Date Hours’18), 0, (18-‘User Templates’.’2 Start Date Hours’))]
4. [(Column Name) – End Date Only] | [(Description) – Find the date in end datetime] | [(Formula Syntax) – DATETIMEVALUE(CONCAT(DAY(‘Working hours’.’End DateTime’), “-“, MONTH(‘Working hours’.’End DateTime’), “-“, YEAR(‘Working hours’.’End DateTime’)), “dd-MM-yyyy”, ,”datetime”, “GMT”)]
5. [(Column Name) – End Date Hours] | [(Description) – Find the hours in end datetime] | [(Formula Syntax) – DATEDIFF(‘Working hours’.’4. End Date Only’, ‘Working hours’.’End DateTime’, “MINUTE”)/60]
6. [(Column Name) – Downtime in End Date] | [(Description) – Find downtime in end datetime] | [(Formula Syntax) – IF(OR(‘User Templates’.’5. End Date Hours’18), 0, (‘User Templates’.’5. End Date Hours’-9))]
7. [(Column Name) – Network Days between start and end date] | [(Description) – Network days between start datetime and end datetime] | [(Formula Syntax) – NETWORKDAYS(‘User Templates’.’Start DateTime’, ‘User Templates’.’End DateTime’, “”, “0000011”)]

8.1. [(Column Name) – Difference between start datetime and end datetime in hours] | [(Description) – Difference in start date and end date in hours] | [(Formula Syntax) – DATEDIFF(‘User Templates’.’Start DateTime’, ‘User Templates’.’End DateTime’, “HOUR”)]

8.2. [(Column Name) – Difference between start datetime and end datetime in Day] | [(Description) – difference between start datetime and end datetime in days] | [(Formula Syntax) – DATEDIFF(‘Working hours’.’1 Start Date Only’, ‘Working hours’.’4. End Date Only’, “DAY”)] 9. [(Column Name) – Start Date +1 Day] | [(Description) – Adding start date by 1 day] | [(Formula Syntax) – DATEADD(‘User Templates’.’1 Start Date Only’, 1, “DAY”)] 10. [(Column Name) – End Date -1 Day] | [(Description) – Reducing end date by 1 day] | [(Formula Syntax) – DATEADD(‘User Templates’.’4. End Date Only’,-1, “DAY”)] 11. [(Column Name) – Total Network Date between 9 and 10] | [(Description) – Network days between start date+1 and end date-1] | [(Formula Syntax) – NETWORKDAYS(‘User Templates’.’9. Start Date +1 Day’, ‘User Templates’.’10. End Date -1 Day’, “”, “0000011”)] 12. [(Column Name) – Total Working Hours in 11] | [(Description) – Total working hours network days considering 9 hours per day] | [(Formula Syntax) – User Templates’.’11. Total Network Date between 9 and 10’*9] 13. [(Column Name) – Total Downtime Hours] | [(Description) – Total working hours] | [(Formula Syntax) – User Templates’.’12. Total Working Hours in 11’+’User Templates’.’3 Downtime in Start Date’+’User Templates’.’6. Downtime in End Date’]

How to create formulas? #

Steps to create Formulas on Gathr Analytics :

1. Select the container on which formula needs to be created.
2. Go to the Data Panel.
3. Click on the three dots present on the upper right corner of the Data panel.
4. Select Add Formula from the menu. A broad formula creation panel will open up on the top of your screen.
5. Gathr Analytics formulas are created (syntax) almost in a same way as in MS Excel.
6. Create and name your desired formula and click on Apply. Your formula will be created.
7. You can drag and drop your formula column in the same container or create a different formula container.

What do the dashed lines represent in Execution Graph at Formula Nodes (Green Nodes)? #

Dashed lines in the Execution Graph of Gathr Analytics represent the datasets that are used to create that formula to which the lines connect from respective data sets. The data set with which a formula completes the dashed line loop is the data set where the resultant formulas column gets added.

There are three types of Formula Nodes present in the Execution Graph.

1. Formula Node, which refers to the column/s of the same data set where the formula is being added.
2. This type of Formula Node represents formulas, which make use of columns from different dataset and the resultant formula column gets added in the dataset where the formula is being added
3. This type of formula node doesn’t refer to any column of any dataset and gets added in the dataset where dashed lines make connection

How to delete a formula column? #

To Delete any formula column, go to your data panel and open the data set in which that formula result column sits. Go to that particular formula result column and open the column menu by clicking on a 3 dot menu there. You will get the DELETE option there.

Kindly note, the dependent operation or formula columns based on the deleted column will be affected.

I am getting a “Cyclic Dependency Error” while creating a formula. What does it mean? #

Cyclic Dependency arises in a situation when a user tries to create an arithmetic or lookup formula using 2 other columns whose entities depend on the values of each other.

For Example:

Column B is a formula result which uses elements of Column A. Now if you try to create a formula using columns A & B, it might give you a circular dependency error in certain cases. In other cases, there might be 2 formula columns – Column B and Column C where B uses Column A values and C uses Column B values. Now, if you try to use both these columns in certain formulas, it might give you a circular dependency.

The arithmetic formulas (like SUMIF) doesn’t work on my column containing numbers? #

For any arithmetic formula to work properly on a column, the column should meet 2 basic criteria:

1. The column’s data type should be Integer
2. The column should not be multi-valued

If the above criteria are met, the arithmetic formula should work fine using this column. To check these criteria, Select the column, Got to Business Type & Show Existing Business Type.

Can I use REGEX within any functions like REPLACE? #

As you are trying to use REPLACE function of REGEX, please note, we cannot use the above mentioned Function in Gathr Analytics. We can only SEARCH entities using Special Characters of REGEX. Of course, REGEX can be used with Klera’s function.

I have long string of values in every cell of a column. Need to separate these values to multiple columns (like Split to Columns in Excel). How to do it? #

In order to achieve this, you can use TOKENIZE function in Gathr Analytics. It divides text around a specified character or string and puts each fragment into a comma separated values. TOKENIZE, separates every word of the string value based on defined single token or a set of tokens. Everything falling between tokens would become a single entity. (Just like it happens while defining parameters for Text to Column in Excel)

Syntax: TOKENIZE(column, delimiter, [split_by_each], stopwords, [include_system_stopwords]) For example: TOKENIZE(EMPLOYEE.ADDRESS, “, -“, true, “-$#%&”, true)

I have multiple values against common entities. How to use VLOOKUP if I need all the values against an entity? #

To get all the values from different rows against a common entity through a LOOKUP function, use VLOOKUPALL.

This function is different from simple VLOOKUP where normal VLOOKUP only fetches the first value from different rows, VLOOKUPALL fetches all the values from different rows and renders the result in a multi-valued column.

VLOOKUPALL(search_key, search column, output column)

How to convert EPOCH Date Time format into common format? #

You can refer the attached image of formulas to convert EPOCH format into known date format. An EPOCH in millisecond has 13 digits and in seconds has 10 digits. The Final Date is your resultant column.

Can you provide few Regex examples which are commonly used? #

Few commonly used REGEX for different purposes are as follows:

1. To find all the values after a special character: REGEX(Data set.Column, NULL, “”Special Character ([^:\s]+)””)
2. To find all the values before a special character REGEX(Data set.Column, NULL, “”^[^Special Character]+””, true)
3. To find all the values between 2 special characters REGEX(Data set.Column, NULL, “”\Special Character.*?\Special Character””)
4. To convert a string of multiple lines to a single line string REGEX(Data set.Column’, NULL, “”[^\r\n]+””)

Can I do mathematical operation on multivalued columns? #

It is advisable not to do a mathematical calculation on a multi-valued column.

To use the values of a multi-valued column, you should first apply REMOVE DUPLICATE operation on the selected columns which will fetch all the values in different rows.

To use REMOVE DUPLICATE, Select multiple columns then Right Click and Go to FORMULA. Here you will get the option of REMOVE DUPLICATE. This will result in a new data set with all the values in a cell distributed in separate rows.

I am not getting the formula which I need. Is there a way to create custom formulas? #

If any desired formula is needed urgently and is not present in Gathr Analytics. Then you can make use of Klera’s integration with PYTHON.

A user can write a formula in PYTHON script and then integrate that in Gathr Analytics so that the created formula (function) starts showing up in Gathr Analytics.

The limitation is that this formula will only be available to the user who created it.

Please get in touch over a call to learn how to integrate PYTHON with Gathr Analytics. We are coming up soon with documents on it

APPLYCLUSTERING is not fetching the correct clusters as I needed. Can I regulate the clustering parameters in the formula? #

APPLYCLUSTERING is a formula that makes use of analytics service and renders the clustering algorithm in an easily applicable formula. Use the following parameters & adjust them accordingly to get the desired output.

1. Cluster Count: The number of clusters discovered in each clustering pass. The higher the value of this parameter, the larger the total number of clusters.
2. Maximum Document Coverage Target: The percentage of input documents to be put in clusters. Determines the percentage of documents the clustering engine should assign to clusters. After each clustering pass, the clustering engine will check if the required document coverage has been achieved. If so, it will not perform further clustering passes. The required document coverage may not always be achieved, especially if the maximum number of clustering passes is set to a low value.
3. Maximum Word Document Frequency: The maximum document frequency allowed for words as a fraction of all documents. Words with document frequency larger than maxWordDf will be ignored. For example, when maxWordDf is 0.4, words appearing in more than 40% of documents will be ignored. A value of 1.0 means that all words will be considered, no matter in how many documents they appear.
4. Minimum Cluster Size: Determines the minimum allowed size of a cluster size.

I have multiple AND & OR conditions to be used in a formula. Do I have to write multiple AND &/or OR formulas for that? #

There is no need to use multiple AND/OR functions. You can put more than two conditions in these formulas by simply putting commas (,) between them. E.g. AND(a=b,c>d,e<f)

Can I create nested formulas in Gathr Analytics? #

Yes, Nested Formulas can be created in Gathr Analytics.

I need a lookup result value on the basis of combination of values of both the columns. Can I put two columns as parameters in Vlookup formula? #

LOOKUP formulas don’t allow more than one parameter (primary key) against which results could be fetched. If you want to fetch data on the basis of more than one column, we suggest you do the following:

1. Concatenate the columns in first data set to fetch a new column
2. Concatenate the similar columns in the second data set from where you want to fetch the result (don’t concatenate the one which needs to be fetched)
3. Now, you can use the above 2 columns for LOOKUP as the combination is as good as putting the parameters.

I have a multi valued column. How to count the number of values in each cell of those columns? #

To count the number of values in a multi-valued column, take the following approach:

• Select the multi-valued column and primary column against which these multiple values are coming up.
• First step is to perform remove duplicate operation on these selected columns.
• A new data set will generate with all values in multiple rows against the same primary entity
• Then, apply a COUNTIF formula on the column to count the no of records against the primary column

This will give you the total count of values for the given column.

I am getting an error message while creating a Vlookup formula which says – “Parameter should belong to the data set where the custom column is getting added.” Why is this message coming up and is there something which I am doing wrong? #

This message comes up when you select a container or a data set to create formula in that data set but use column(s) of other data set.

Can I refresh selective formulas through Re-Execute Formulas? #

No, you cannot select a particular formula for re-execution.

1. You can either re-execute all formulas of the exploration from the File Menu, or,
2. Re-execute formulas of a container/data set from the Container/data set Menu, or,
3. Manually edit each formula for re-execution.

If I delete a data set, will the columns in other data sets which are making use of columns of the deleted dateset (through any of the Lookup formulas) will be automatically deleted or do I have to go on and delete each and every formula selectively? #

The dataset cannot be deleted if any of the columns of the dataset are being used in the formula of other datasets. In order to successfully delete the dataset, first remove all the columns are used in the formula of datasets. Then only dataset can be deleted.

How to do conditional counting on Gathr Analytics? #

Conditional counting can be done on Gathr Analytics using the COUNTX formulas which are COUNTIF and COUNTIFS. Both the formulas are based on the logic of counting items in a column based on condition of other column (COUNTIF) or multiple columns (COUNTIFS).

For example, Counting of Jira Issues for a particular Assignee X should be in the formula as – COUNTIF(Issues,Assignee,”=”,”X”), or

Counting of Jira Issues for a particular Assignee X and particular Project PQR should be in the formula as – COUNTIFS(Issues,Assignee,”=”,”X”,Project,”=”,”PQR)

How to do conditional addition on Gathr Analytics? #

There are 2 simple formulas by which you can do the conditional addition in Gathr Analytics. SUMIF and SUMIFS.

SUMIF returns the sum of a column, filtered by the criteria.

For example: SUMIF(EMPLOYEE.SALARY, “>50000”) SUMIFS returns the sum of a column depending on multiple criteria. For example: SUMIFS(EMPLOYEE.SALARY, EMPLOYEE.AGE, “<30", EMPLOYEE.GRADE, “="&GRADE.GRADE)

I already have an algorithm in Python, can i reuse it in Gathr Analytics? #

You can import Python script into Gathr Analytics and enable functionality quickly. You can upload the script which can do any simple use case or even write your own algorithm for AI/ML etc.

What does a Remove Duplicate do? #

Remove Duplicate Operation does the following tasks by generating a new dataset:

1. Remove Duplicate Rows from the existing data and keep only the unique rows
2. Convert Multivalued Columns to Single Valued Columns by rendering those multi values to a new row for each value. The condition is that the data type of the multivalued column should be Multivalued.

Note – To check the Data Type of any field/column, Select that Column > Right Click > Go to Business Type > Select View. In the resultant dataset of information about the selected field, the Is Multivalued field should be YES.

The below Images contain an example of both the work that Remove Duplicate does Dataset A Column A Column B Column C X P J Y Q K Y Q K Z M, N, O S, T, U After Remove Duplicate Operation Column A Column B Column C X P J Y Q K Z M S Z N T Z O U

Do we have Transpose option in Gathr Analytics? #

Yes, Gathr Analytics does provide the Transpose Data option. You can use these steps to perform Transpose option.

1. Select data from the grid on which you would like to perform Transpose Data operation
2. Then right click by keeping the data selected and then go to Data Option in the list
3. Select Transpose Data option as shown in the image b…Read more

Does Gathr Analytics support nested formulas? #

Yes, Gathr Analytics supports NESTED FORMULAS where you can nest multiple functions (same or different) with proper logic to create a computed column. Example – IF(Column A = “”X””,””Category 1″”, IF(Column A = “”Y””,””Category 2″”, “”Category 3″”))”

Does Gathr Analytics support multivalued columns? #

Yes, Gathr Analytics supports MULTIVALUED COLUMNS. Such Multivalued Columns can be converted to Single Valued Columns as well as some Formula Functions support Multivalued Columns (like VLOOKUPALL) To learn more about how to check whether the column is Multivalued or not and how to convert it to Single Valued, access the post about Remove Duplicates and Business Types in community.

Can I lookup more than one matching entities using a single LOOKUP? #

Yes, you can look up more than one matching entity for a LOOKUP. Because Gathr Analytics supports multivalued columns, hence, if there is more than one matching entity against a value for LOOKUP, then we advise using VLOOKUPALL to get all the corresponding values in the form of a multivalued column.