topic Re: Grouping in a pivot table in Excel in Qlik NPrinting

Grouping in a pivot table in Excel

PuriVelasco — Thu, 25 Feb 2021 10:45:27 GMT

Hello,

I am using NPrinting version Jun 2019. I am able to do pivot tables correctly but I don't know how I can group values of dates.

Example: Excel group values of columns which format is date, in days, months, years, etc., but NPrinting not.

Any help or suggestion?

Thank you in advance!

Re: Grouping in a pivot table in Excel

Lech_Miszkiewicz — Thu, 25 Feb 2021 12:11:42 GMT

can you share what do you mean by "grouping" dates - sharing an example or what you are trying to achieve would help to give you the proper answer.

Re: Grouping in a pivot table in Excel

Ruggero_Piccoli — Thu, 25 Feb 2021 12:36:17 GMT

Hi,

When you develop a Qlik NPrinting report template you can use all the Microsoft Office features. I think the field that contains a date is exported instead as a text so Excel doesn't understand that it can be managed as a date. I suggest you to try to add a calculated column that transforms the da in date format in the native table you are using a s source for the pivot.

Best Regards,

Ruggero

Re: Grouping in a pivot table in Excel

PuriVelasco — Thu, 25 Feb 2021 14:44:16 GMT

Hi,

Excel means by "grouping" which I show in the next image:

You have the context option "Group" and "Ungroup" for dates and this option groups the columns with the same date.

However, in NPrinting the option is there but it doesn't run.

Result for group in NPrinting:

Thank you very much.

Re: Grouping in a pivot table in Excel

PuriVelasco — Thu, 25 Feb 2021 14:49:35 GMT

Hi,

the field in Qlik Sense is a date, besides in NPrinting in the report for that field, I use the same format from origin with check in "Keep same format".

Besides, I have selected date format in Excel.

At the other hand, If I select Preview report I am able to group/ungroup values for that field.

Thank you very much.

Re: Grouping in a pivot table in Excel

Ruggero_Piccoli — Thu, 25 Feb 2021 14:59:52 GMT

Hi,

Did you tried to run a report preview?

This is an Excel error message generated because now Excell sees the tags and errors but tags will be replaced with data in the generated reports.

Best Regards,

Ruggero

Re: Grouping in a pivot table in Excel

PuriVelasco — Thu, 25 Feb 2021 15:03:45 GMT

Yes, I run a report preview. I am able to group the same dates, (with different hours) in the report preview, but I want to group in the designer to do this task automatic.

Now I am grouping manually after I have generated the report.

Thank you!

Re: Grouping in a pivot table in Excel

Ruggero_Piccoli — Fri, 26 Feb 2021 10:27:44 GMT

Hi,

I did some tests and I can reproduce. The only solutions I found are:

recipients have to remove and add manually the field date
you can create calculated columns in the source straight table (for example by using formulas like =YEAR([@Date]) ) and use that columns in the pivot. Creating those calculated columns in the load script will made the report generation faster.

Best Regards,

Ruggero

Re: Grouping in a pivot table in Excel

PuriVelasco — Tue, 02 Mar 2021 12:02:17 GMT

Hi,

Thank you very much for your answers.

I know that I can create calculated columns but I think those are not a good solutions.

If I publish the report, Excel shows the calculated fields for the dates, but I would like NPrinting did the same to do that task automatically.

Re: Grouping in a pivot table in Excel

Ruggero_Piccoli — Tue, 02 Mar 2021 13:23:32 GMT

Hi,

In this case I suggest you to open a support ticket with a link to this conversation. I can reproduce the behaviour you described in my installation so I think developers have to check that to analyze if it is possible to obtain the goal you want.

Best Regards,

Ruggero