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
Contributor II
Contributor II

Using excel formulas and repeats in all lines

Hello,

I have an Excel Report in Nprinting. I created this report by using tables and add objects. This is in the first sheet in my excel report. In the second sheet I have a pivot table by using first sheet datas.

In the first sheet i want to add 2 columns with excel formulas. In the first column I want to have data by using Vlookup formula from my second sheet pivot table. And in the second column I want to use sum formula (datas from first sheet). I copied my formulas 1000 lines

When I preview my report I see 100 lines.. But my excel formulas stays on my first line, they doesn’t repeated on during 99 lines. And on the 101. Lines and belows my formulas repeats.

How can I do to repeat automatically this formulas on all lines ?

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Using excel formulas and repeats in all lines

Hi,

Thanks. You have to create a native Excel table as datasource for the pivot like explained in https://help.qlik.com/en-US/nprinting/February2020/Content/NPrinting/ExcelReports/Pivot-table-Excel..... I mean this https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e49... kind of Excel table. 

Then you add the formulas in the two latest columns and the formulas will be replicated on every row.

Native Excel Table with Calculated Column.png

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.

View solution in original post

Tags (2)
9 Replies
Highlighted
Employee
Employee

Re: Using excel formulas and repeats in all lines

Hi,

It is not clear to me what is your scenario, so some screenshots will help.

You are creating a pivot table so you inserted the source data in a level (I suggest to avoid this method) or you added a native Excel table.

If you are using levels you can add the formula between the level tags and it will be repeated on every row.

If you are using a native Excel table you insert the formulas in new columns that are part of the table so the formulas will be repeated every row.

If you want to retrieve data from the pivot table, you need to use the Getpivotdata() function.

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
Contributor II
Contributor II

Re: Using excel formulas and repeats in all lines

Hello Ruggero,

Thank you for replying. I downloaded a document to explain my problem.  I hope I manage to explain it. I'm new in Nprinting.  I don't use levels (I have no idea about this). Getpivotdata function doesn't work.

I want to see my formulas in all lines on preview mode.

Best regards,

Kubra

 

Highlighted

Re: Using excel formulas and repeats in all lines

Hi,

There are 2 options:

  1. use levels to create your table
  2. insert --> table (excel table) to autopopulate formulas fo entire column

all is explained in https://help.qlik.com

Since you are new to NPrinting i suggest properly study all excel tutorials from there.

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

Re: Using excel formulas and repeats in all lines

Hi,

Thanks. You have to create a native Excel table as datasource for the pivot like explained in https://help.qlik.com/en-US/nprinting/February2020/Content/NPrinting/ExcelReports/Pivot-table-Excel..... I mean this https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e49... kind of Excel table. 

Then you add the formulas in the two latest columns and the formulas will be replicated on every row.

Native Excel Table with Calculated Column.png

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.

View solution in original post

Tags (2)
Highlighted
Contributor II
Contributor II

Re: Using excel formulas and repeats in all lines

I'll check and try these. Thank you very much.

Best Regards,

Kubra

Highlighted
Contributor II
Contributor II

Re: Using excel formulas and repeats in all lines

Hi,

Thank you. I'll study all and try to use levels also.

Best regards,

Kubra

Highlighted
Employee
Employee

Re: Using excel formulas and repeats in all lines

Hi,

Use the native Excel table, it is faster than levels.

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

Re: Using excel formulas and repeats in all lines

Very good point @Ruggero_Piccoli !!!

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
Contributor II
Contributor II

Re: Using excel formulas and repeats in all lines

Hello,

Thank you very much!!!  It worked.  🤗  I didn't use levels. I used "Creating Excel pivot tables" link you sent me yesterday.

Thank you!

Best regards,

Kubra