QlikView - RangeSum Function
Qlikview tutorial 2020-11-19 19:59:38
QlikView - RangeSum Function The RangeSum () function in QlikView is used to selectively sum over selected fields which is not easily achieved by the sum function. It can take expressions containing other functions as arguments and return the sum of those expressions. Input data Consider the monthly sales figure as shown below. Save the data with the Monthly_sales.csv file name. Month, sales volume March, 2145 April, 2458 May, 1245 June, 5124 July, 7421 August, 2584 September, 5314 October 7846 November 6532 December 4625 January 8547 February 3265 Load The above data is loaded into QlikView memory using the editor. Open the editor from the File menu or press Ctrl + E . Choose the Table files option in the File data and find the file containing the above data. Edit the loadout to add the following code. Click OK , then click Ctrl + R to load the data into QlikView"s memory. LOAD month, [Sales volume] FROM [C: Qlikview data monthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ", ", msq); Applying the RangeSum () function With the above data loaded into QlikView"s memory, we edit it to add a new column, which will give a sliding sum of the month wise sales volume. For this, we also take the help of the peek function discussed in the previous chapter to keep the value of the previous record and add it to the sales volume of the current record. The next one gets the result. LOAD Month, [Sales Volume], rangesum ([Sales Volume], peek ( "Rolling ")) as Rolling FROM [C: Qlikview data monthly_sales.csv] (txt, code page is 1252, embedded labels, the delimiter is ", ", msq); Creating a leaf object Let"s create a Table Box leaf object to display the data generated by the data above. Go to the menu Layout → New sheet object → Table box . The following window appears in which we mention the Table Title and select the mandatory fields to display. Click OK to display the data from the CSV file in the QlikView table area as shown below.