Incorta formula functions

Time Series Functions

Function Signature Description Example ago() ago(date exp, int rollBack, string datePart) Calculates an aggregated value from a specified date back to a specified period of time, such as a month, quarter, or a year ago. Fiscal Year to Fiscal Year Analysis
sum(sales invoice dollars, ago(fiscaldate, 1, 'FiscalYear')
or
Year to Year analysis
sum(sales invoice dollars, ago(fiscaldate, 1, 'Year')
toDate() toDate(date exp, string datePart) Calculates an aggregated value from a specified start date to a specified period of time. sum(sales invoice dollars, toDate(fiscaldate, 'Quarter')
or
sum(sales invoice dollars, toDate(fiscaldate, 'FiscalYear')
toDate() with ago() ago(toDate( date exp, string datePart), int rollBack, string datePart) Calculates an aggregated value from the summation of the ago and toDate function values.

Ranking Functions

Function Signature Description rank() rank(groupBy, orderBy) Returns the rank of a value based on its order in the grouped values. Multiple rows can share the same rank resulting in nonconsecutive ranks. denseRank() denseRank(groupBy, orderBy) Returns a consecutive rank of each row based on the order of the grouped values index() index(groupBy, orderBy) Returns the index of rows based on the order of the grouped values

LAG(), compares current and previous month numbers.

LEAD()

SUBSTRING()

FINDLAST()

example: Only display the lead characters in a string - data looks like this 9211-5832 or AKDO-78DD

substring(
    DATAFIELDNAME, 
    0, 
    (findLast(
        "-", 
        DATAFIELDNAME
    ))
)

Output will look like this: 9211 and AKDO

For Example for a company "X" we have data for every months (measure column : Amount) from Mar-2022 and if I am running the report in May-2024, then my reports should show amount distribution like :

Mon-YY Mar-22 Apr-22 May-22 … Dec-22 Jan-23 … Jan-24 .. May-24 Company X 100 100(Mar amount) + 60 (april amount) i.e. 160 160 (previous month amt)+100 (May amount) i.e. 260 amount till Nov-21+ amount of Dec-22) amount till Dec 2022+ Jan23 amt amount Till Dec-23 + Jan-24 amt amount till Apr-24+May-24 amt

and this calculation will repeat for every company.

Year or Year Analysis: https://community.incorta.com/t5/dashboards-analytics/insights-over-result-sets/ta-p/1118