Using excel formulas and repeats in all lines

[kzakut]

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 ?

[Ruggero_Piccoli]

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 

[kzakut]

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

[Lech_Miszkiewicz]

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

[Ruggero_Piccoli · Accepted solution]

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.

Best Regards,

Ruggero

[kzakut]

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

Best Regards,

Kubra

[kzakut]

Hi,

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

Best regards,

Kubra

[Ruggero_Piccoli]

Hi,

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

Best Regards,

Ruggero

[Lech_Miszkiewicz]

Very good point @Ruggero_Piccoli !!!

[kzakut]

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