Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ts3st1000
New Contributor

Transpose columns in the display

Hi,

In the database, I have a large table that i load in the load editor part of which looks like this:

 

CountryYearSales
Japan201010
Japan201111
Japan201212
Japan201313
Japan201414
Germany2010100
Germany2011110
Germany2012120
Germany2013130
Germany2014140
UK20101000
UK20111100
UK20121200
UK20131300
UK20141400

I am trying to use the data above to display the following table:

CountryTotal SalesYear 2010Year 2011Year 2012Year 2013Year 2014
Japan601011121314
Germany600100110120130140
UK600010001100120013001400

Could you please suggest a solution to this. Thanks for the help.

Regards,

Ra

18 Replies
Ivan_Bozov
Valued Contributor

Re: Transpose columns in the display

Hello!

Use pivot table:

Capture.PNG

ogster1974
Honored Contributor II

Re: Transpose columns in the display

Agreed pivot your data.

ts3st1000
New Contributor

Re: Transpose columns in the display

I have just tried pivot table and i think i did everything right but i see only the totals and nothing within the table for the sales by country.

Any other suggestion to represent this please.

ts3st1000
New Contributor

Re: Transpose columns in the display

Thanks Andy. I have just tried the pivot table and i think i did everything right but i see only the totals and nothing within the table for the sales by country.

Any other suggestion to represent this please.

ogster1974
Honored Contributor II

Re: Transpose columns in the display

can you post an image?  you have the data to show it.

ogster1974
Honored Contributor II

Re: Transpose columns in the display

Set your country field as a row and your year field as a column and then sum(sales) as your measure.

You should have all you need to produce what you want.

ts3st1000
New Contributor

Re: Transpose columns in the display

Thanks Andy. that is exactly what i have done. I have attached a screenshot. the data i am using here is similar to that i provided in this question but a bit confidential hence hiding it.

i dont know why i only get the aggregate number per year at the top.

if you can please think of a solution or an alternative way, that would great. thanks again.Capturess.PNG

ogster1974
Honored Contributor II

Re: Transpose columns in the display

Suggests to me your sales data isn't associated by country properly just year. 

ogster1974
Honored Contributor II

Re: Transpose columns in the display

To test this remove the year from your piviot and see what happens I think you'll see sales summaries on the null country value.