Custom sorting in PixelPerfect tables

    Data in PixelPerfect tables are sorted in the same way as they are in the source QlikView document or Qlik Sense app. This article illustrates how to set a different sorting criteria directly in a PixelPerfect template. In the first part by using an existing column and in the second part the sort order is based on a calculated column.

     

    Create a new PixelPerfect template with a level and a details band as show in the picture:

    010.png

    If you run a preview, you will see that data are sorted by Segment in alphabetical order.

    020.png

    To sort the data by Sales Representative, the second column, add a new group header:

    1. Right click on the Detail1 band
    2. Select Insert Band
    3. Click on Group Header
    4. Don't insert any column header in this band. We will add headers after.

    030.png

    Now we start the steps to set the Sales Rap Name column as sorting criteria. While keeping the GroupHeader1 selected, click on the three dots on the right of the Group Field property. The GroupField Collection Editor appears.

    040.png

    Click on Add on the left bottom area.

    050.png

    Open the Field Name drop down menu and select the Sales Rap Name column.

    060.png

    Confirm by clicking on Ok. Run a preview, data will appear sorted by Sales Rap Name.

    070.png

    Now we will create a calculated column that will be used as new sorting criteria. It is not necessary to made this new column visible in the report in order to use it as sorting criteria. This is just an example.

    1. Do a right click on the table name in the Levels node
    2. Click on Add Calculated Field
    3. Set New_Sort_Order as (Name)

    080.png

    Open the Expression editor by clicking on the three dots on its right.

    Insert the formula Round(1/[NPPsJt],5 )*100000. Remember that this is just an example and that in a formula you can use only the fields that are available in the Levels node.

    090.png

    Confirm by clicking ok.

    If you want you can create the space to add this new calculated field in the table, but it is not mandatory. We add it to have a more clear explanation.

    100.png

    To use the new column New_Sort_Order as sorting order:

    1. Select the GroupHeader1 band
    2. Open the Group Fields property

    110.png

    Set the column New_Sort_Order as Group Field.

    120.png

    Confirm by clicking on OK and run a preview.

    130.png

    To add the column headers, do the following:

    1. Right click on GroupHeader1
    2. Select Insert Band
    3. Click on Group Header
    4. Drag and drop the fields from the Levels node by keeping the right button of the mouse pressed
    5. Set the headers format as you prefer

    140.png 

    Run a preview

    150.png