topic Using excel formulas and repeats in all lines in Qlik NPrinting

Using excel formulas and repeats in all lines

kzakut — Tue, 14 Apr 2020 12:17:46 GMT

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 ?

Re: Using excel formulas and repeats in all lines

Ruggero_Piccoli — Tue, 14 Apr 2020 13:02:12 GMT

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

Re: Using excel formulas and repeats in all lines

kzakut — Tue, 14 Apr 2020 14:03:01 GMT

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

Re: Using excel formulas and repeats in all lines

Lech_Miszkiewicz — Tue, 14 Apr 2020 14:22:02 GMT

Hi,

There are 2 options:

use levels to create your table
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

Re: Using excel formulas and repeats in all lines

Ruggero_Piccoli — Tue, 14 Apr 2020 14:22:42 GMT

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.htm. I mean this https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c kind of Excel table.

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

Best Regards,

Ruggero

Re: Using excel formulas and repeats in all lines

kzakut — Tue, 14 Apr 2020 14:49:43 GMT

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

Best Regards,

Kubra

Re: Using excel formulas and repeats in all lines

kzakut — Tue, 14 Apr 2020 14:51:35 GMT

Hi,

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

Best regards,

Kubra

Re: Using excel formulas and repeats in all lines

Ruggero_Piccoli — Wed, 15 Apr 2020 13:40:45 GMT

Hi,

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

Best Regards,

Ruggero

Re: Using excel formulas and repeats in all lines

Lech_Miszkiewicz — Wed, 15 Apr 2020 14:06:19 GMT

Very good point @Ruggero_Piccoli !!!

Re: Using excel formulas and repeats in all lines

kzakut — Wed, 15 Apr 2020 16:25:42 GMT

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