NPrinting - Conditional Formatting Within a Level - Qlik Community

lgtsotrsdh · ‎2015-05-05

Hi,

I’m having issues with configuring an NPrinting report in Excel. I’m trying to add conditional formatting based on values within a level.

I have four lines that repeat in a level. Within this level, I would like the values in N9:N12 to be compared to values C11 and C12 through conditional formatting. When the level repeats, the values in N13:N16 should be compared to the values in C14 and C15, and so on.

When I add the conditional formatting to the template and do a preview of the report, the conditional formatting is all jumbled between various cells in column N, and the compare cells don’t update for the report (i.e. always C11 and C12). I don’t know if this is because of the extra lines required to add the level beginning and ending.

Has anyone had any experience with a report like this and can provide any tips to what I'm missing?

Thanks,

Scott

Winter255 · ‎2019-09-24

Hello from 2019... I have same issue... NPRINTING Conditional formatting works incorrectly between the layers...

It's just applies the same rule for all layers... So if I have low values at layer 1 and high at layer 2...it will apply rules for layer 1 & 2...(so layer 1 values will get dark and layer 2 light)....

Also I had to use cells menu to rebuild simple heat-map chart (Nprinting ignores "horizontal" setting for expressions)

And I had to clear QV formatting for each cell (300 cells in total) because if you use "cells" menu to modify chart orientation to horizontal...Nprinting will keep old column width...and your chart more likely will be wide and ugly

...So I had to remove "keep source formats" from edit cell menu...1 by 1, 300 times...(there is no preferences for all cells)

Its insane!!!... Such trash in 2019 trying produce simple heatmap report for last 3 weeks data...

Ruggero_Piccoli · ‎2019-09-24

Sir,

May 5ft 2015 the most recent version of Qlik NPrinting maybe was 16.1 or similar. Now we are at 19. Between the two tare are some small changes.

Please:

- avoid to add post to a so old conversation. Too many things are changed in 4 years of software development. Four years old information could have no value today. Open a new conversation.

- you must specify what version you are using

- what kind of template are you developing? Please add screenshots.

We need to be able to reproduce the issue and to understand it clearly to help you.

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.

Winter255 · ‎2019-09-25

I use 19 (June) version at the moment.

So, there is an issue with row to row conditional formatting and levels and issue with QV export for "horizontal" setting (for straight table expressions).

By row to row I mean each row has own conditional formatting coloring cells with 2 color gradient based on each particular row min and max values. If there is no levels it works perfectly well.

If I use levels to multiply such tables Nprinting will multiply conditional formatting as well.

Let's say there are 2 tables (new sales and repeated)... With conditional formatting applied on row 1 table 1 (layer1) and row 1 table 2 (layer2)

So at the end we have min and max between 2 tables Row 1.

Workarounds tried- TAGS, Tables...The only workaround I could suggest at the moment is to use Nprinting excel data sheet as Raw Data (its possible to hide it) and use excel INDIRECT() function to move values into another sheet with predefined conditional formatting.

To link at content between pages use:

INDIRECT($B$5&"!"&CELL("address",A1))

In this example sheet name is located at cell B5 (if it' contains the gaps formula will look like:

INDIRECT(" ' "&$B$5&" ' !"&CELL("address",A1))

Bear in mind that NPrinting template will show #REF! as formula result.

Ruggero_Piccoli · ‎2019-09-25

Hi,

I suggest to:

- open a support ticket with a link to this conversation so developers can deeper investigate the issue. Please include also the source document and template to help use to replicate.

- did you tried to create a native Excel table and apply the formatting rules to it?

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.

Winter255 · ‎2019-09-30

Hello Ruggero.
Unfortunately the file I working with contains sensitive info. So I can't publish it.
Sure it's possible to apply native excel conditional formatting.
The full story short - I had to replicate some company report from 'SQL->excel' to QV dashboard.
Initial excel file had conditional formatting: data table as matrix where every line used as conditional formatting range for 2 color gradient applied based on values.
So I replicated entire logic in QV (to replicate conditional formatting I used colormap function and it works well with QV dashboard).
Apart of it I had to transform data to horizontal style (when expressions in columns and dim's in rows).
After a while some executives had intention to receive daily report from QV.
So, if I use nprinting to spread this object from QV (its CH type) - I have following issues:
1: If I use 'picture' menu in Nprinting menu - data is quite wide and I have picture with scrolling bar 😄 😄 😄 Of course this is just the scrolling bar of static picture 😄
2: If I use 'table' menu in Nprinting menu- Nprinting ignores the "horizontal" setting for CH object. And exports data to excel vertically (when expressions are in rows and dim's in columns)
3. If I use 'cell' menu to transform the data in the way I need cell by cell (its quite longer but still ok if you use excel to mass-produce cell names)
the outcome I have- VERY VIDE COLUMNS for expressions which is ugly and unacceptable.
So I have to use option 3 and remove QV colormap and rest formatting 😄 😄
And finally I have issue with levels when creating nPrinting Template if I apply conditional formatting in template between the layers.
So 1 simple tasks to complete 4 workarounds failed...

Winter255 · ‎2019-09-30

So line by line conditional formatting looks like

Winter255 · ‎2019-09-30

And the outcome if I use Nprinting with levels:

Nprinting just binds the range together for both tables.

Excel tables, Tags and etc failed to solve that isssue. The only way is to use INDIRECT() and hide initial data sheet

Ruggero_Piccoli · ‎2019-09-30

Hi,

Thanks for the details. I'm more convinced that this need to be reproduced and investigated here by a developer. At least the part about the interaction between conditional formatting and levels.

So, please, open a support ticket and include a link to this conversation, report template and the Qlik Sense app needed to reproduce.

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.