topic Re: Excel conditional formatting lost when using nprinting 17 levels in Qlik NPrinting

Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Tue, 12 Dec 2017 17:25:43 GMT

Am generating an excel file using nprinting 17. I conditionally format a couple of cells, within in a table(that i created using columns from a qliksense object in the excel template) such that if the value in one cell is greater than the other cell, its shaded green or else red. It was working fine.

The moment i introduced a level and then wrapped it around the previously created table - the level functionality was working fine as it was spanning out tables in the same worksheet for the various values of the level that I added. However, the conditional formatting was totally last. I can no longer see the cells shaded as per the conditional formatting rule that I set up earlier.

Is this a known issue or am i missing something here ? Help.

Re: Excel conditional formatting lost when using nprinting 17 levels

darrell_tobin — Tue, 12 Dec 2017 18:11:59 GMT

Do you have an example of the excel formula and the table you can share so we can have a look please.

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Tue, 12 Dec 2017 18:27:45 GMT

Hi Darrell,

Attached screenshots of the excel that i am using and the formula that is relevant.

When I don't include the <RMLevel>, the table prints out fine and the colors look correct. The moment I pull the <RMLevel> to the template, the level works fine, as in the table you see is repeated for each of the values of the RMLevel - but the color coding is totally missing.

Re: Excel conditional formatting lost when using nprinting 17 levels

ogster1974 — Tue, 12 Dec 2017 19:07:47 GMT

Have you tried defining your table and referencing that in your formula rather than cells. The issue will be the level moves the cell start for each table instance. by defining and referencing your table it should hold the formatting.

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Tue, 12 Dec 2017 19:14:14 GMT

Hey Andy,

Not completely sure what you meant there when you say "Defining your table". The table that you see in my screenshot is straight out of qliksense. I added it to the tables node in excel and then dragged each of the table columns manually into the excel template.

didn't really get the "Defining and referencing your table" either. Can you explain a bit please.

Re: Excel conditional formatting lost when using nprinting 17 levels

ogster1974 — Tue, 12 Dec 2017 19:33:36 GMT

You can highlight your table area and give it a name for reference. I've used it to identify the data source for pivot tables using nprinting.

I'll have a go at it tomorrow morning and get an example working as it should work.

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Tue, 12 Dec 2017 19:39:24 GMT

ok.Thanks.

But am interested in formatting only one of the columns in the table ! Should I be referencing a table ?

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Tue, 12 Dec 2017 23:32:38 GMT

Andy,

just some more detail.

My goal is to repeat the table that you're seeing for each value of the RMLevel. So am nesting the table within the RMLevel tags- After which I expect to something like this as in the attached image here. I expect to see only the Avg.Team Score column shaded in either red/green in comparison with the avg.comparison group score on the right.

Also, I am creating a page for each category, so the above table is repeated across worksheets also, for each value of the page-fyi. Anyways, if we can make it work for one sheet-it should work for all the pages as well. thanks !

Re: Excel conditional formatting lost when using nprinting 17 levels

Ruggero_Piccoli — Wed, 13 Dec 2017 10:49:37 GMT

Hi,

I suggest you to apply the Excel conditional formatting to two rows (the green and the red ones in the picture) in the template. Then add a <deleterow> tag at the beginning of the unuseful row so it will be deleted from the produced report.

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 as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Re: Excel conditional formatting lost when using nprinting 17 levels

darrell_tobin — Wed, 13 Dec 2017 12:01:12 GMT

Have you tried just adding the tags in row 11 and 12 without the excel table function. Do you really need this data in an excel table?

I have a similar report I have built that uses conditional formatting in a table (BUT NOT USING EXCEL TABLE JUST TAGS IN CELLS) just like yours and the formatting works OK.

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Wed, 13 Dec 2017 15:58:13 GMT

I think this approach works only for 1 table. In my case, i have a level across which the table gets repeated.

I did something like this

=INDIRECT("Table1[@[Avg. Team Score]]")<INDIRECT("Table1[@[Avg. Comparison Group Score]]")

When the table gets repeated for each level and across each page(since i have a page too), the table name changes to Table2, Table3 etc.. so the above conditional formula is not relevant. Its shading the cells in the successive tables based on the Table1.

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Wed, 13 Dec 2017 16:11:43 GMT

hi rpc‌

I applied conditional formatting and prepared the template with levels just like your example as below

and applied conditional format rules in D12 and D13 cells as below

and I still lose the formatting-output comes as below.

am I missing something here ?

Re: Excel conditional formatting lost when using nprinting 17 levels

Ruggero_Piccoli — Thu, 14 Dec 2017 10:11:21 GMT

Hi,

You have to apply the Excel conditional formatting ruled to both cells D12 and D13 and, as you correctly did, add the <deleterow> in the row number 13.

Applying the Excel formatting rules to two rows is mandatory because it indicates to Qlik NPrinting that the rules must be copied to all added rows during the report production.

If you add the formatting rules only to only row (for example the cell D12) you will find that rules only on the cell D12 of the generated reports.

Best Regards,

Ruggero

---------------------------------------------

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Thu, 14 Dec 2017 14:10:49 GMT

Hi rpc‌

I did do that. I selected D12 cell and applied the D12 conditinoal formatting and then selected D13 and applied the D13 conditional formatting that you're seeing.

and yes I specified <deleterow> in the beginning of row 13. Am a bit confused as to what am missing still.

Re: Excel conditional formatting lost when using nprinting 17 levels

Ruggero_Piccoli — Thu, 14 Dec 2017 14:26:29 GMT

Hi,

What version of NPrinting are you using? I tested in November 2017 that resolves a bug exactly on this point

From November Release Notes:

Conditional formatting rules would not expanded correctly in Excel reports

Conditional formatting rules would not expand correctly in Excel reports. If you applied a level, conditional formatting would not be applied properly to the correct cells.

So update to November and this should resolve.

Best Regards,

Ruggero

---------------------------------------------

Re: Excel conditional formatting lost when using nprinting 17 levels

Anonymous — Thu, 14 Dec 2017 14:33:50 GMT

I think that's it ! Am on 17.3.1 !

Thanks a ton !

Re: Excel conditional formatting lost when using nprinting 17 levels

Ruggero_Piccoli — Thu, 14 Dec 2017 14:36:42 GMT

You are welcome 🙂