How to build or create Pixel Perfect Mixed Format Report in Qlik Nprinting?

[mstoler]

Hello,

I will need to build a Mixed Format Report which has Units and Dollars.  I have set up the buttons to use a hidden field so that it will be compatible with On-Demand.  On-Demand is my target.  The buttons are used to set the variable. 

_ShowAmountTable:
Load * Inline [
_ShowAmountUnitsDollars
1,
2
];

tag Field _ShowAmountUnitsDollars with '$hidden';

Set vShowAmountUnitsDollars = "=If(GetSelectedCount(_ShowAmountUnitsDollars)=0,2,only(_ShowAmountUnitsDollars))";

// 1 is Units, 2 is dollars

I am looking for some suggestions on how to get Pixel Perfect to format as Units and Dollars?  I know that Conditional Formatting does not handle this (this we discussed in a previous post).

Below is a mock-up of my Dashboard.  I am considering in Pixel Perfect creating a report for Units and then Dollars and then using the ability to show/hide.  I was wondering if anyone else has any good suggestions.

Thank You,

Michael 

Qlik Sense Dashboard

Units

Dollars

[Lech_Miszkiewicz · Accepted solution]

Hi @mstoler 

When we spoke last time I actually thought you were going to have more complex report as this one seems to be very simple, being:

• multidimensional straight table
• just grand total summary
• measures: units or @$

For such requirement i would just do following::

Create measure in Qlik Sense - number formats can be set to your liking):

• Pick($(vShowAmountUnitsDollars),
  Text(Num(Sum(Units),'#,##0.0;-#,##0.0')),
  Text(Num(Sum(Amount),'$#,##0.0;($#,##0.0)'))
  )
• 
• In NPrinting bring this table as level
• Create NPrinting Formula for labell and Grand Total
  • f_Total = Pick($(vShowAmountUnitsDollars),Text(Num(Sum(Units),'#,##0.0;-#,##0.0')),Text(Num(Sum(Amount),'$#,##0.0;($#,##0.0)'))) - this is the same formula as for a measure in Qlik Sense
  • f_Label = Pick($(vShowAmountUnitsDollars),'Qty','Net $')
  • 
• Result On Demand:
  • 

If the requirements are just like you have described it then there is no point using conditional hide/show on report template segments in PixelPerfect. Why would you need to do that? Since we are using Pixel Perfect template there is no harm in values being converted to text. Having just a grand total as per your description in the post makes it easy - using simple formula and adding it to header or footer solves the problem and keeps maintenance to minimum.

BY THE WAY - you may want to open images and .gifs in new window as they are a lot more clear than directly in this post.

Let me know if there are any additional requirements regarding the actual layout of the report or if suggested solution is good enough.

cheers

Lech

[mstoler]

Hello,

Thank You.

This works great.  

Thank You,

Michael

[mstoler]

Hello,

It seems that I will be heading towards creating a separate table (similiar to the original table) for NPrinting.

For these reasons:

1. I have dual fields and I will need a column for the text equivalent as the dual will appear only as a number.

2. I need to use the text in the expressions.  I don't really want this in the version the users will use.

I can put this table at the end of the Dashboard.

Is this typical when you get involved with complicated reports?

Michael

[Lech_Miszkiewicz · Accepted solution]

This is very common Michael. You can imagine how often you need to have some special columns added either in form of text for display or number/dual for sorting. 

Where possible I try to put tables built for NPrinting purpose in hidden sheet (if you are on the Qlik Sense version which supports that) or in container without tabrow where it is hidden). 

regards

Lech