Financial Functions

Financial functions are used to calculate payments and interest rates. They are particularly useful for project evaluation.

This article will cover the basic principles of working with financial functions in the data load editor and cisualisations, including:

  • The general function syntax
  • Working with financial functions in the data load editor
  • Working with financial functions in charts

For a complete, up-to-date list of financial functions, please see the Qlik Sense help pages here.

The financial function syntax

The syntax will differ depending on the function. For up-to-date syntax, please do refer to the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

It isn’t always easy to decipher the syntax provided in the help pages linked to above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages.

To explain the syntax, we will use an example function.

Example 1: FV

This function returns the future value of an investment.

Copy code 


FV(rate, nper, pmt [ ,pv [ , type ] ])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there are 5 elements. See the Qlik help pages for explanation of each function element.
  3. Anything between square brackets [] is an optional parameter. In this function, the optional parameters are [pv] and [type]. If you do include the parameters, DO NOT include brackets.

An example of this function being used with the optional parameters included could be:

Copy code 


FV("Rate","Periods","Payment","Present Value","Payment Type")

The above function will return the future value of the project given the values in the fields used as input parameters. The optional parameters assume a non 0 present value and a payment at the start of the month.

An example of the function without using optional parameters may be:

Copy code 


FV("Rate","Periods","Payment")

The above function will return the future value of the project given the values in the fields used as input parameters. As the optional parameters are omitted, a present value of 0 an end of period payment schedule is assumed.

How to use financial functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use these functions in the load. The rules are:

  • The functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • The functions can be nested and combined with other types of functions

A simple project comparison script may look like this:

Copy code 


PROJECTS:
LOAD * INLINE [
Project Name, Rate, Periods, Payment, Present Value, Payment Type
A, 0.01, 12, -20, 50, 1
B, 0.03, 6, -15, 25, 1
];

PROJECTS_FV:
LOAD 
    "Project Name",
    FV("Rate","Periods","Payment","Present Value","Payment Type") as "Project Future Value"
    
RESIDENT PROJECTS;

The load results in 2 tables: “PROJECTS” that contains information on each project and “PROJECTS_FV” that contains the project name and the calculated future value of each project.

How to use financial functions in visualisations

Financial functions in visualisations calculate exactly the same as they do in the load script. The rules are as follows:

  • The functions can be nested and combined with other types of functions.
  • The functions can be used as dimensions or measures depending on your needs.