Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. READ MORE

Summarize Time Columns in a Qlik NPrinting PixelPerfect Reports

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Ruggero_Piccoli
Support
Support

Summarize Time Columns in a Qlik NPrinting PixelPerfect Reports

Last Update:

Apr 19, 2022 5:14:53 AM

Updated By:

Sonja_Bauernfeind

Created date:

Feb 4, 2020 6:50:56 AM

In this tutorial, we will use calculated fields to manage Qlik Sense columns with time data in Qlik NPrinting PixelPerfect reports. We will create formulas to extract days, hours and minutes from a duration column then we will calculate the sum and display it in the dd hh:ss format.

Environment:

Qlik NPrinting 

 

Before we get started:

To follow this tutorial you need to be familiar with PixelPerfect templates development process. We will not explain the details that are already covered in the official Help Site PixelPerfect reports.

Qlik Sense stores each date, time, and timestamp found in data as a date serial number as explained in Date and time interpretation.

The tutorial uses the Qlik Sense demo app Help Desk Management from the link  https://demos.qlik.com/qliksense/HelpdeskManagement.

 

Steps:

  1. Create a Qlik NPrinting connection to that app and wait until the cache is generated.
  2. To limit the number of rows used during the report development process, create a new filter. Set the Case Owner field to the value Gerardo E. Ferroni and the Subject field to New Laptop Request.
  3. Create a new PixelPerfect report and call it, for example, PxP Sum Time Format from Sense Tutorial, and add the filter.  Then open the template in the Qlik NPrinting Designer.
  4. Add the Case Details straight table to the Levels node, create the related Details band and insert the Case Number, Case Owner and Duration columns. Add the GroupHeader and you should have a template like the following:

    020.png

  5. If you run a report preview the duration column (last one on the right) will be displayed as a double number, but we want to see the days, hours and minutes. Applying the format string to the column will not work.

    030.png

  6. We will use PixelPerfect calculated fields to extract the Days, Hours and Minutes from the details row and show the totals. The integer part of the date and time is the number of days.
  7. Right-click over the table name and select Add Calculated Field. In the Expression property add the formula Floor([emtEjp]). In the (Name) property insert Days. Drag and drop the Days box into the Details band. The result will be similar to:

    040.png

  8. Run a report preview and you will see the Days number.
    050.png

  9. To calculate the hours we will subtract the Days to the duration and multiply the result by 24. Add a new calculated field, call it Hours and insert the expression Floor(([emtEjp]-[Days])*24). You can use the name of an existing calculated field into another. We suggest to do this to keep the expressions more readable. Drag and drop the Hours field into the details band. The template will be similar to:

    060.png

  10. Run a report preview:
    070.png

  11. You can compare the results with the numbers in Qlik Sense to be sure they are correct. It is not mandatory to drag and drop all the calculated columns into the template. We are doing this for didactical reasons. You could also develop a single, more complex, formula instead of doing all single steps.
  12. To calculate the minutes we need to subtract Days and Hours to the Duration and multiply by 60. Create a new calculated field, call it Minutes and insert the formula Floor((([emtEjp]-[Days])*24 - [Hours])*60). Then drag and drop the Minutes tag in the details band.

    080.png

  13. Run a report preview.

    090.png

  14. To add the totals, start by adding the GroupFooter band. Drag and drop the Duration tag and set Group and Sum as summary. This will calculate the total of the duration in numeric format.

    100.png

  15. Run a preview

    110.png

  16. To calculate the days of total duration create a new calculated field, call it Sum_of_Case_Duration_Days and add the expression Floor(Sum([emtEjp])). Then drag and drop the tag into the GroupFooter band.

    120.png

  17. Run a preview

    130.png

  18. To calculate the hours of the total duration, create a new calculated field, call it Sum_of_Case_Duration_Hours and add the expression Floor((Sum([emtEjp])-[Sum_of_Case_Duration_Days])*24). Drag and drop the new tag in the GroupFooter.

    140.png

  19. Run a preview

    150.png

  20. To calculate the minutes of the total duration, create a new calculated field, call it Sum_of_Case_Duration_Minutes and add the expression Floor(((sum([emtEjp])-[Sum_of_Case_Duration_Days])*24 - [Sum_of_Case_Duration_Hours])*60). Drag and drop the new tag in the GroupFooter band. 

    160.png

  21. Run a report preview:

    170.png

  22. We explained how to calculate days, hours and minutes step by step for didactical reasons. It is possible to create a single expression that shows all the results in a single string. Create a new calculated field, call it Sum_of_Case_Duration and add the expression Concat([Sum_of_Case_Duration_Days],' ',[Sum_of_Case_Duration_Hours],':',[Sum_of_Case_Duration_Minutes] ). The expression uses the previous calculated fields, you don't need to add them into the template to use them in another formula. Drag and drop the new tag in the GroupFooter band.

    180.png

  23.  Run a report preview

    190.png

  24. It is possible to obtain the same result in a single step that includes all the calculations in a more complex formula. Create a new calculated field, call it Sum_of_Case_Duration_Single_Step and insert the expression Concat(Floor(Sum([emtEjp])),' ',Floor((Sum([emtEjp])-[Sum_of_Case_Duration_Days])*24),':',Floor(((sum([emtEjp])-[Sum_of_Case_Duration_Days])*24 - [Sum_of_Case_Duration_Hours])*60) ). The expression was created by replacing the Days, Hours and Minutes variable names in the previous expression with their detailed calculations. Drag and drop the new tag in the GroupFooter band.

    200.png

  25. Run a report preview

    210.png

 

Labels (1)
Comments
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well done mate...i knew there must be a workaround possible 🙂

On the other hand it is a shame we can't rely on built in sing format for time series aggregation.

Ruggero_Piccoli
Support
Support

The faster solution is to jump directly to the final formula and customize it. The advantage is that this solution doesn't need to develop a custom script in C#, JavaScript or what else, so it is feasible to more developers. It is also an example of calculated fields and they can be used in many other situation. 

Thanks for the feedback.

QSMACHBI
Contributor III
Contributor III

Perfect !

 

Thank you very much Ruggero. 

BinayKumarSah
Contributor III
Contributor III
Thanks for sharing
Version history
Last update:
‎2022-04-19 05:14 AM
Updated by: