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

[Gerdinho22]

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 🙂

[Frank_S]

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....

or

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

Hope this helps...

[Lech_Miszkiewicz · Accepted solution]

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.  

[Gerdinho22]

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"?

[Gerdinho22]

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

[Ruggero_Piccoli]

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

[Gerdinho22]

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.

[Gerdinho22]

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.

[Ruggero_Piccoli · Accepted solution]

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