Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Extract total cells from all Sheet Level

Hi everyone!

I'm trying to solve a problem in Qlik Nprinting that I don't know if it has a solution.

In my problem, I have a report divided by a page level. In each one, all the possible cases will appear in the report, and there will be a cell prepared with the formula sum () that will make the sum of all those lines created in each sheet.

Captura1.PNG

Well, what I need to do is to take those totals from each sheet and take them to a new normal sheet (without any page level created).

When I put the link to that cell on my normal sheet, it only shows the total from the first sheet. But of course, I need all the totals of all the sheets and I don't know if this can be achieved.

For example, if I put it to be done by the page level, the number of totals created is correct, but the references are not correct.

Captura2.PNG

Captura3.PNG

Does anyone know if this can be done and how?

 

Thanks!

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Extract total cells from all Sheet Level

Hi,

Obviously NPrinting development is actually combo of Qlik Sense and NPrinting development and the approach I would take for the purpose of this exercise would be to create an object directly in Qlik Sense application which would return the required values. 

but if you insist...you could use a Excel sheet with macro...

  • create template xlsm
  • load that template
  • create EVAL function in Module 

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert text strings to formulas

1
2
3
4
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

 

  • use PAGE tag to create references by concatenating required cells and formulas (in my case I used vlookup)

 

 

=CONCATENATE("=VLOOKUP(",CHAR(34),"Tender Total TTW",CHAR(34),",",B2,"!A:B",",","2",",","0)")

 

 

  • use custom EVAL () function to evaluate it post report generation

 see short video for demonstration (attached).

 

Depending on report requirements you may find this overcomplicated and could stick to simpler solutions. Definitely reference to the sheet will not flow with the <Page> tag the way you tried to use it, so that is no-no!!!

 

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

7 Replies
Highlighted

Re: Extract total cells from all Sheet Level

Hi,

Obviously NPrinting development is actually combo of Qlik Sense and NPrinting development and the approach I would take for the purpose of this exercise would be to create an object directly in Qlik Sense application which would return the required values. 

but if you insist...you could use a Excel sheet with macro...

  • create template xlsm
  • load that template
  • create EVAL function in Module 

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert text strings to formulas

1
2
3
4
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

 

  • use PAGE tag to create references by concatenating required cells and formulas (in my case I used vlookup)

 

 

=CONCATENATE("=VLOOKUP(",CHAR(34),"Tender Total TTW",CHAR(34),",",B2,"!A:B",",","2",",","0)")

 

 

  • use custom EVAL () function to evaluate it post report generation

 see short video for demonstration (attached).

 

Depending on report requirements you may find this overcomplicated and could stick to simpler solutions. Definitely reference to the sheet will not flow with the <Page> tag the way you tried to use it, so that is no-no!!!

 

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

Highlighted
Partner
Partner

Re: Extract total cells from all Sheet Level

Hello Lech.

Thanks for your answer, I think it is a very good solution. I'm testing it, but I can't get it to work. Adapting the "concatenate" and the "vlookup" function to my example, Nprinting creates the Excel and I get the string correctly. Also, if I put it manually (the string) it gives me the result correctly. The problem I have is that when I create the function with VBA code, I'm doing something wrong because it doesn't return me anything.

I'm creating the module as you indicate. Do I have to save it in any special way?

Module.PNG

The result, when I put the Eval () function, is that it doesn't return anything to me when transforming the string. It also puts it between { }, which isn't the same result as yours. And this is not from Nprinting alone, I create it in any normal Excel and the function created still doesn't work for me.

Plantilla1.PNG

 

Plantilla2.PNG

 

Result1.PNG

 

But if I put the text manually, it returns the value correctly.

Result2.PNG

I also have some doubts with this:

  •  The totals that we extract from the different pages, will they be updated on our summary page if they are modified on their corresponding page?
  •  Although the creation of the Eval () function does not work for me, does the module stay saved forever? Because if I close the report and reopen it, the module that I had saved has disappeared. If every time I want to make the report I have to create the function it's not good for the solution (I suppose I'm doing something wrong too)

Thanks for everything again.

Greetings.

Highlighted

Re: Extract total cells from all Sheet Level

Hi,

So in my previous comment i said that you need to prepare xlsm template. 

It means that you need to create xlsm excel file first outside of the nprinting. On that ocasion you can create module in it and save it like that. 

before creating report you have an option to choose a template file - this is where you point to your xlsm file (excel with macro)

so the steps are:

1. go to excel and create xlsm file with module, save it as xlsm file

2. create nprinting report and use a previously created file as template.

3. remaining steps are the same.....

1.png

 

Remeber to preview it as XLSM file and in publish task also use XLSM format to allow for macro run.

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.
Highlighted

Re: Extract total cells from all Sheet Level

your other questions:

I also have some doubts with this:

  •  The totals that we extract from the different pages, will they be updated on our summary page if they are modified on their corresponding page?
    • I believe they will update as EVAL is just like any other formula.
  •  Although the creation of the Eval () function does not work for me, does the module stay saved forever? Because if I close the report and reopen it, the module that I had saved has disappeared. If every time I want to make the report I have to create the function it's not good for the solution (I suppose I'm doing something wrong too)
    • It is important to use xlsm template to create whole report in NPrinting. Module will stay in it. You saw on the short video i did that it stayed, but the output was .xlsm
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.
Highlighted
Partner
Partner

Re: Extract total cells from all Sheet Level

Hi Lech,

Correcting the wrong steps I had made, I have advanced, but it still doesn't work.

To make it easier, I have created an Excel (xlsm) outside of Nprintng where I'm testing the formulas (once they work pass it to Nprinting). I have the same module that you created, but it keeps giving me an error.

Captura2.PNG

Captura1.PNG

 

Captura3.PNG

and doing the string like a normal function works ..

Captura4.PNG

I have thought that the created formulas do not read them to me, but creating a simpler one it works correctly.

Captura5.PNGCaptura6.PNG

Therefore, it's a problem that the function to go from string to formula does not work for me. And I'm going a little bit crazy trying everything I see on Google, but nothing works.

Any help is still welcome.

 

Thanks.

Highlighted
Employee
Employee

Re: Extract total cells from all Sheet Level

Hi,

Qlik NPrinting adds the rows it needs during the report creation so original rows are moved down and you lost the references.

Instead of working only on VBA did you evaluated the possibilities to:

- develop a Qlik Sense object with the totals you need and import it into the template?

- use the template Variables or Formulas to calculate the totals and insert them into the template?

Note that VBA code is never executed during reports generation so you need to associate it with an event, for example on opening the .xlsm file, or ask to the recipients to execute 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.
Highlighted
Partner
Partner

Re: Extract total cells from all Sheet Level

Hi!

Thank you both for your responses. What you propose Ruggero, I'm aware of these possibilities, but I need them to be linked to the excel totals, because if they are modified in the sheets, they also be updated in the Summary sheet.

I've finally got it to work. It was a problem of how Excel and VBA collects the code (?). The Lech example has concatenate () function working with ','. In my case, I had to put ";" for the function to work, but that the string on VBA part didn't work.

At the end the string that has worked for me has been the following:

=CONCATENATE("VLOOKUP(";CHAR(34);"DN Total";CHAR(34);",";"'";B3;"'";"!D:H";",";"5";",";"0)")

And with the same code in VBA it works.

Thanks very much both for your help!

Greetings.