Adding Totals grouped by Currency

[Geoffrey_Moss]

Hi

In a PixelPerfect report I have a detail table of values in a mixture of currencies. Each value in the detail table is displayed with various dimensions including the currency code.

I need to display a Total for each currency in the Group beneath the table but I haven't been able to get the structure right to display the Totals by Currency.

Firstly, is this possible and secondly, if so, how do I structure the levels to provide the required layout?

Thanks

[Ruggero_Piccoli · Accepted solution]

Hi,

First solution: Create a main level by currency and nest a level with table used as data source. Then create subtotals and totals as described in https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/PixelPerfectReports/Add-Totals-Subto.... This is simpler solution but you will have a table on generated report ordered by currency.

Secondo solution: a short tutorial. The idea is to use PixelPerfect formulas to create calculated fields with subtotals. I create a field with the summary of the product category Babywear and you can do the same by currency. Screenshots are made on the final template so you will se the results from the beginning but you have to build them.

1 - Create a Pixelperfect template and add the data source straight table as a level as usual

2 - Right click on the table and select add calculated field

3 - Create a calculated field with a condition to filter only data of a specific category (currency in your case)

4 - Drag and drop the new field in the report where you created the native table as usual

You can run a preview to check that calculated values are correct. It is not mandatory to add the calculated column in the final report, I added it for didactical reasons. 

5 - Add the Group Footer band where you will insert the totals. Drag and drop the calculated field and set the totals calculation by opening the summary widonw as explained in the linked tutorial.

6 - Run a preview

7 - You can add a calculated fields with a summarization function the Group Band alone without its column visible. For example add a new calculated field to calculate the percentage of Babywear of the total sales

7.1 - Drag and drop it in the template 

7.2 - Run a preview

Third solution: like the previous one but you can add the calculated column by currency in the source QlikView document or Qlik Sense app. This will reduce the reports generation time by moving calculation on the data reload (Qlik NPrinting will not need to recalculate the new fields for every report) but needs an update of the source and of the connection cache.

Best Regards,

Ruggero

[Frank_S]

Very nice @Ruggero_Piccoli!

[Ruggero_Piccoli]

Thanks @Frank_S 🙂