topic Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect in Qlik NPrinting

sales value calculation (every seller per month, quarter, year) in PixelPerfect

Gerdinho22 — Tue, 26 Nov 2019 16:03:40 GMT

Hi everyone,

I'm trying to create a report, which lists me the sales values of every seller. In Qlik Sense I've solved this easily with a pivot table (att.)

I'd like to have a detailed selling view, similar to this table:

Seller	Quarter	Month	sale value	...
SellerJohn	1	1	5	...
SellerJohn	1	2	5	...
SellerJohn	1	3	5	...
SellerJohn	1		15
SellerJohn	2	4	6
SellerJohn	2	5	6
SellerJohn	2	6	6
SellerJohn	2		18
SellerJohn	3	7	4
SellerJohn	3	8	4
SellerJohn	3	9	4
SellerJohn	3		12
SellerJohn	4	10	5
SellerJohn	4	11	5
SellerJohn	4	12	5
SellerJohn	4		15
SellerJohn			60

It should create a long PDF.

Due to the limitations of the nprinting tables (not "pivot-able"), this issue confuse me a lot. Does anyone have an idea, how to get it on?

thank you in advance 🙂

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Frank_S — Tue, 26 Nov 2019 18:43:54 GMT

Hi @Gerdinho22

So although you can export the QS pivot table using NPrinting, the NPrinting report output is similar to straight table data which cannot be expanded/collapsed.

As a work around, do the following:

1. Copy your Qlik Sense Pivot table and convert the copy to a straight table.
2. Save the QVF and then reload the NPrinting Connection to ensure the new table is visible to NPrinting
3. Create an NPrinting Pivot table using Qlik Sense (or QlikView for QlikView users) straight table data.

Here is the tutorial as to how to do this:

https://help.qlik.com/en-US/nprinting/November2019/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm

https://help.qlik.com/en-US/nprinting/November2019/Content/NPrinting/ExcelReports/Excel-pivot-table-report.htm

Hope this helps...

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Lech_Miszkiewicz — Tue, 26 Nov 2019 21:54:44 GMT

It will be a lot of work, but you could also build Pixel Perfect pivot table using QlikSense straight table as a data and formatting rules to create "PivotTable like" view by creating regural pixelPerfect table from levels and hiding dimensions values (changing value colour to the same as background colour and by changing default border settings to conditional).

I went through this process and it is quite challenging to get everything right and you would have to first prepare your straight table right so it can support such solution.

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Gerdinho22 — Thu, 28 Nov 2019 13:31:58 GMT

Hi everyone,

these were good approaches, but the results weren´t satisfying. Now I´ve tried to solve this issue by creating levels and sublevels to create the bonding (both in att.)

Do you thin it could cause some problems, or "if the result is correct, it is correct"?

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Gerdinho22 — Tue, 07 Jan 2020 15:58:54 GMT

Hi everyone,

the listing is working with levels and sublevels (L1->seller, L2-> year, L3-> quarter, L4->month). I have now the issue, that every year is listed also (back till 2008), but I want to have just the current year. I created a filter on the field for my year:

Name: Jahr

Values->Evaluate value: =year(today())

But it seems this filter is ignored or obvisously wrong

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Ruggero_Piccoli — Tue, 07 Jan 2020 15:53:14 GMT

Hi,

It could be an issue related to the data type. Try to use:

Values->Evaluate value: =text(year(today)=

Try also:

Values->Evaluate value: year(today)

without the beginning = character.

Best Regards,

Ruggero

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Gerdinho22 — Wed, 08 Jan 2020 13:11:16 GMT

Hi Ruggero,

I´ve checked the data model and there is $numeric $integer shown. I´ve also tried with:

'num(year(today()))'

'=num(year(today()))'

'num(year(now()))'

'=num(year(now()))'

year(...)/=year(...)

...

I think almost every combination, even changed the request type for the value-control, but the error still raises.

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Gerdinho22 — Thu, 09 Jan 2020 15:29:55 GMT

Hi everyone,

my errors suddenly disappeared and now even my advanced filters are working. There where no syntaxerrors in the filters, but I have made changes in my QS-Apps (i.e. static values in variables) and it seems that NP has references to the old data chached. By creating the report the reference is unlocated and the error occurs. After a reload of the meta data in my connection, everything is working fine and the new data is fetched also.

Re: sales value calculation (every seller per month, quarter, year) in PixelPerfect

Ruggero_Piccoli — Fri, 10 Jan 2020 09:47:09 GMT

Hi,

After each change in the source Qlik Sense app you must refresh the connection cache and close and reopen the Qlik NPrinting Designer otherwise the previously generated cache is used.

Best Regards,

Ruggero