Overlapping Pivot tables in excel sheet

[imrasyed]

Hi Experts,

I have two Pivots in Npritning but upon preview they are overlapping?

Is there any workaround for the issue iam facing.?

[Lech_Miszkiewicz]

Hi @imrasyed 

You should probably ask this question in microsoft forum. If you are referring to native excel pivot tables then they are limited by excel functionalities so therefore question is not related to NPrinting really, right?.

On the other hand if you are referring to something else then please provide details so we can replicate the issue on our end and be able to provide you with more accurate advice.

cheers

Lech

[imrasyed]

Hi Lech,

I am using native pivot tables.And i dont think microsoft forum has the capability or ability to understand nprint as the table data is dynamic overhere its not like fixed number of rows.

I would have definitely approached microsoft forum if its fixed number of rows and still overlapping which is not happening.

This issue is related to nprint as I am using qliksense object(fields with tags) which should be automatically expanding the rows based on the Qliksense table data.

Please have a look at the above pic where i have two pivot tables one below the other.

Upon expanding the data is getting overlapped.

Please suggest a workaround.Highly appreciate your help.

[Lech_Miszkiewicz]

Well,

it is mocrosoft question and i disagree with statement that it has anything to do with nprinting. 
You get exactly the same issue if you create 2 pivot tables and you dont leave enough space between them for all required data. As soon as you put more records and refresh pivot table tables will overlap when doing it directly in excel. 

all waht you do in nprinting is sourcing data which is then pushed to pivot tables.

so the solution is to leave as many cells as required between top and bottom table. 

and again-it is standard excel behaviour  (not nprinting). 

cheers

[Lech_Miszkiewicz]

More on the fact that it is strictly microsoft question. 

you can take away Nprinting factor from it simply by asking question what will happen if you put 2 pivot tables one below another one pointing to 2 sources. 

assume you initially have 1 record in each source, then ask what will happen if the source grows. This is quite often a scenario where excel source table is directly linked to a Sql and uses query to update data on refresh

[imrasyed]

Thanks Lech for the Solution.

But the problem still persists like table rows are dynamic.

Sometimes it is 30 and sometimes it is 50.

I wonder if this is only with Pivot table and not with the straight table(as straight table one below the other work perfectly fine).

Thanks

[Lech_Miszkiewicz]

Well, 

It is kind of obvious, right?

When you are building straight tables using nprinting you have properties like "Expand range" available so additional rows are added automatically for each row of data created below the table. therefore table below is pushed down

Now pivot tables are completely different as the values in them are not added by nprinting. NPrinting is only populating source data (typically a straight table) and pivot tables are just reading what data is available in source table and expand accordingly. This is why i said it is not NPrinting question, as the only link to NPrinting is that you populate straight tables with data and pivot tables are refreshed once data is populated.

Now I understand that number of rows is dynamic and depends on combinations of values in dimensions and measures. The workaround for this will be to create an object/table in Qlik Sense/QlikView (whatever you are using) which will return the required number of rows to effectively populate data below top pivot table which will cause bottom pivot table to be pushed down (note <Dim1_1> tag in my case). In addition you will need to enable in excel pivot tables option [DATA]-->[Refresh data when opening file] to refresh pivot table so it is not affected by records populated below. You can make this additional data very small (say font size 1) and the same font colour as background colour so it does not affect final result. 

cheers

[imrasyed]

Thanks Lech will work on proposed solution.

Appreciate your help.