Summarize Time Columns in a Qlik NPrinting PixelPerfect Reports

[Ruggero_Piccoli]

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:
   
   
   
   
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.
   
   
   
   
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:
   
   
   
   
8. Run a report preview and you will see the Days number.
   
   
   
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:
   
   
   
   
10. Run a report preview:
    
    
    
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.
    
    
    
    
13. Run a report preview.
    
    
    
    
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.
    
    
    
    
15. Run a preview
    
    
    
    
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.
    
    
    
    
17. Run a preview
    
    
    
    
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.
    
    
    
    
19. Run a preview
    
    
    
    
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. 
    
    
    
    
21. Run a report preview:
    
    
    
    
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.
    
    
    
    
23.  Run a report preview
    
    
    
    
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.
    
    
    
    
25. Run a report preview
    
    

[Lech_Miszkiewicz]

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]

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]

Perfect !

Thank you very much Ruggero. 

[BinayKumarSah]

Thanks for sharing