Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Export pivot table to excel from Qlik Sense Desktop

Hi,

I'm trying to export my pivot table to excel. I've read that I'm supposed to right click, and select copy to clipboard. However, I don't have that option, only "Copy" and that doesn't work. Getting hold of the values is enough for me - I don't need a pivot in excel.

Thanks, and best regards,

Felix 

Tags (3)
7 Replies
robert99
Valued Contributor II

Re: Export pivot table to excel from Qlik Sense Desktop

QS does not have an export feature for the PT

Use a straight table (you can quickly covert a PT copy to a ST and then export )

Not applicable

Re: Export pivot table to excel from Qlik Sense Desktop

Hi,

Thank you for your swift answer.

How do I convert the PT into a ST? I've found how to export the ST, but converting the PT beats me.

Best regards,

Felix

robert99
Valued Contributor II

Re: Export pivot table to excel from Qlik Sense Desktop

Try

Make a copy of the PT

then edit this copy

-Make all selected fields rows

-Drag straight table app option onto the PT

-Convert to ST

Not applicable

Re: Export pivot table to excel from Qlik Sense Desktop

Hi,

Thanks, that conversion was very easy once you know how to do it!

However, it didn't do as I had hoped. I want a ST for the following layout:

Row: Shop

Column: Year

Measures: Total sales

I manage to get what I want with a PT, but when converting I get:

Column: Shop

Column: Year

Column: Total sales

This means that I get the following as a ST (example)

Shop A; 2012; 1 000

Shop A; 2013; 1 500

Shop B; 2012; 3 000

but what I want as a ST and I have in the PT, is

               2012     2013

Shop A    1000     1500

Shop B    3000

I hope I've managed to explain my issue.


Best regards,

Felix

robert99
Valued Contributor II

Re: Export pivot table to excel from Qlik Sense Desktop

The only way to so this is

Remove year as a dimension (only have shop as a dimension)

Add two expressions (or how many years you have) with a set analysis as an expression

Sum ({<Year = {2012}>} sales  (expression 1)

Sum ({<Year = {2013}>} sales  (expression 2)

Sum ({<Year = {2014}>} sales  (expression 2)

Not applicable

Re: Export pivot table to excel from Qlik Sense Desktop

Hi,

We're getting there!

Two final (?) questions:

  1. I have the date for the sales (e,g 2012-12-31) for 2012. I thought the below expression would help me, as I've used Year(Date) previously, but now I get an error: "Error in set modifier expression".
    Sum({<Year(Date)={2013}>}Sales)
  2. I have a "number of sales" too, is there a reason to believe that replacing sum with count will not work?


Best regards,

Felix

robert99
Valued Contributor II

Re: Export pivot table to excel from Qlik Sense Desktop

Count will work fine as well

Re year (Year (Date) =  will NOT work (if year (Date) = 2013 might work though

But the better option is to set up a calendar in script

Year (Date) as Year,

Month (Date) as Date,