Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
gerald_lebret
Partner - Creator
Partner - Creator

Calculated fields result not showing in reports

Hi all,

I am pretty new to Nprinting and need someone's help.

I am using pixel perfect to create a report.

I have created a lot of calculated fields that I want to use in a table.

For instance, I have a formula for the number of days in january and a formula with the sales for january. Then I created a calculated field to calculate the sales/day.

For some reason, If I add into my table the 2 formulas and the calculated field. It works fine.

However, if I only show the calculated fields, it does not appear at all.

Can anyone please help? I am a bit stuck.

I also have a problem because whenever the result of a calculated field = 0, Nprinting report shows blank and not 0

The first problem is the most important one and any help would be highly appreciated.

Thank you.

Best regards.

Gerald

 

Labels (2)
2 Solutions

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well, I am not sure whats the problem?

Yes you need to use set analysis for each month, thats the workaround we know we have to take.

So to create what you have shown I would create an island dimension in my data model:

 

 

Load
    Dual(Dim_txt,Dim_no) as Dim
;
Inline
    [
        Dim_no, Dim_txt
        1,      Sales
        2,      Cost
        3,      Margin
    ]
;

 

 

I would also create variables for my measures as in this case I can use variables with parameters:

 

 

Set vSales  = Sum({<Month={$1}>}Sales);
Set vCost   = Sum({<Month={$1}>}Cost);
Set vMargin = $(vSales($1))-$(vCost($1));

 

 

 

And after reload I would:

  • create a straight table object
  • add Dim as my dimension
  • add my measures (obviously parameter 1 i used can only be used in scenarios where you have numerical months... but at the same time you may just put "Jan" for january... 

 

 

Pick(Dim,
    $(vSales(1)),
    $(vCost(1)),
    $(vMargin(1))
)

 

 

  • you copy that measure 11 more times and change parameter for each of the months and thats it

Is that the approach you were thinking of?

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

gerald_lebret
Partner - Creator
Partner - Creator
Author

Hello,

This a solution but at the moment, I ended up importing all converting the data into a stright table in qlik sense and then I imported all the cells from the table.

View solution in original post

7 Replies
Ruggero_Piccoli
Support
Support

Hi,

What do you mean with "only show the calculated fields"? Could you post some screenshot of the template?

Are you using not supported objetcs https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Qlik-objects-supp...

As alternative you can create calculated fields in PixelPerfect reports https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/PixelPerfectReports/Create-Calculate... or insert them as Formulas.

Best Regards,

Ruggero



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
gerald_lebret
Partner - Creator
Partner - Creator
Author

Hello Ruggero,

No I am not using not suported objects. I know that objects such as pivot table are not supported as such (only as image). Thus, I created formulas and calculated fields in Nprinting.

In my example, I want to calculate the daily number of covers per month in a restaurant. In order to do so, I created a formula for my number of days in a specific month, another formula for my number of covers in that same month and finally a calculated field that divides the number of covers by the number of days.

The result I want for january is 528 which is the result of 10568/20

gerald_lebret_0-1631027140195.png

However, if I don't add 10568 and 20 in my table because I only want 528 in january, here is what's hapenning:

gerald_lebret_1-1631027248016.png

Thanks for your help.

 

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Are you refering to this feature?

https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/PixelPerfectReports/Create-Calculate...

If so, I think this is unfortunatelly "working as designed" and I believe for the calculated column to work in template the components used in calculation need to be also added to the template. They dont have to be visible, but must be added for calculation to work.

 

I am not a big fan of using such solution though as it is hard to maintain, has performance impact and it is difficult to debug. My suggestion would be to create dedicated object directly in Qlik Sense with all the required columns and use them as a source.

cheers

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
gerald_lebret
Partner - Creator
Partner - Creator
Author

Hi,

I am indeed referring to the feature you are mentionning.

It is a lot of work but this is the only way to get the design I want with a table.

Here is an example of what I need to do:

 JanuaryFebruarymarch
Sales    
Cost    
Profit    

 

This is easily done with Qlik Sense via Pivot Table. Unfortunately, as far as I know, pivot table are not supported as object in Nprinting and can only be used as image which forces me to create a a table with 1 column per month and then to create 1 formula per month for sales and 1 formula for cost  per month (through set analysis), and finally 1 calculated field per month for profit (sales-cost).

I haven't found any other way, I would gladly accept your guidance.

Thanks.

 

Best regards.

 

Gerald

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well, I am not sure whats the problem?

Yes you need to use set analysis for each month, thats the workaround we know we have to take.

So to create what you have shown I would create an island dimension in my data model:

 

 

Load
    Dual(Dim_txt,Dim_no) as Dim
;
Inline
    [
        Dim_no, Dim_txt
        1,      Sales
        2,      Cost
        3,      Margin
    ]
;

 

 

I would also create variables for my measures as in this case I can use variables with parameters:

 

 

Set vSales  = Sum({<Month={$1}>}Sales);
Set vCost   = Sum({<Month={$1}>}Cost);
Set vMargin = $(vSales($1))-$(vCost($1));

 

 

 

And after reload I would:

  • create a straight table object
  • add Dim as my dimension
  • add my measures (obviously parameter 1 i used can only be used in scenarios where you have numerical months... but at the same time you may just put "Jan" for january... 

 

 

Pick(Dim,
    $(vSales(1)),
    $(vCost(1)),
    $(vMargin(1))
)

 

 

  • you copy that measure 11 more times and change parameter for each of the months and thats it

Is that the approach you were thinking of?

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
gerald_lebret
Partner - Creator
Partner - Creator
Author

Hello,

This a solution but at the moment, I ended up importing all converting the data into a stright table in qlik sense and then I imported all the cells from the table.

Frank_S
Support
Support

@gerald_lebret

 

That is exactly what you need to do with any NPrinting report (for your specific requirement) in which you need data that exist in a Pivot table....convert it to a straight table and use the straight table in your NP report. Its simply not possible to expand the individual columns/field data for use in your reports. To see this you can add your Pivot Table to and excel report and add straight table to the same excel report.

The pivot table cannot be expanded where as the straight table can. In turn then you can use the individual fields in any NP MS office, HTML and PxP (pixel perfect) report.

 

kind regards...

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!