If you are a relatively new QlikView developer, you may find it difficult to accurately round fractions to whole numbers. QlikView’s Round function is a great function to utilize until you run into calculation issues.
One way to ensure you are accurately rounding to the whole number involves creating a function/variable via an Include file.
Step 1: Create a reusable and universal formatting variable.
For simplicity, I created a text file and named it as “Formatters”. Inside the text file I created numerous variables/functions that will be loaded into all of my QlikView application to ensure the formatting is consistent.
Below is the code used in the include file for Rounding to Whole Numbers:
Set v_WholeNumber = [NUM(NUM(Floor($1) + IF(NUM(Round(Frac($1),.01)) >= .50,1,0)))];
The goal of v_WholeNumber is to separate and evaluate the fractional part of the number. If the fraction, when rounded to 2 decimal places, is equal to or exceeds .50, 1 is added to integer of the field being evaluated.
$1 represents the field that you will evaluate in the script. The exact code for your script is contained in Step 4.
Step 2: Create the Load Statement for the “Formatters” text file in your QlikView Application.
When the Load statement is complete, it will look similar to the following.
$(Include=\...\...\...\includefiles\formatters.txt);
Step 3: Reload your QlikView application.
Step 4: Integrate and utilize the newly created variable/function.
Example Situation and Code:
The application you are creating is comparing a company’s sales performance against the company’s budget. Due to the budget creating process, the Budget Quantity can be fractional while sales occur in complete or whole units (integers).
To convert the company’s Budget Quantity to an integer utilize the v_WholeNumber Function which is now loaded into your application.
Code in Load Script:
$(v_WholeNumber([Budget Quantity])) as [Budget Quantity],
