Select the what I want to see in Pivot table and sorting.

[Anonymous]

Hi QlikView Community

I have one issue I am trying to solve. I have this simple dataset.

Year	Customer	Revenue Code	Revenue	Number of Containers
2015	1	Service	1.764.209	1744
2015	1	Handling	3.340.318	1718
2015	1	Transport	9.530.124	1881
2015	1	Other	6.456.502	934

I want to compare customers and am comparing each customer to revenue/Number of Containers.

1) But I want to only focus on three Revenue Code that is Service, Handling and Transport.

I  want to exclude the other revenue code in my pivot table.

2) Secondly I want to sort the Revenue Code in this order Transport, Handling and Service.

I want my pivot table to look like that

Customer	Revenue Code	Transport	Handling	Service	Total
3		8.176	3.877	2.958	4.619
2		1.146	2.393	2.018	2.460
1		4.811	1.196	3.281	3.542
4		4.492	3.004	6.773	4.434
Total		4.315	2.559	3.578	3.701

regards Darri

[marcus_sommer · Accepted solution]

You could filter your dimension with an expression like:

if(match([Revenue Code], 'Transport', 'Handling', 'Service'), [Revenue Code])

and hide NULL on this dimension and for sorting them you could use a sort-expression like:

pick(match([Revenue Code], 'Transport', 'Handling', 'Service'), 1,2,3)

Another way of sorting could be to influence the load-order of this field with a dummy-load but this isn't an option if you need the sorting one time in this way and another time in that way.

- Marcus

[Kushal_Chawda]

Use set analysis instead of calculated dimension

Create Pivot Table

Dimension:

Customer

Revenue code

Expression:

=Sum({<[Revenue code]={'Transport', 'Handling', 'Service'}>}Revenue)

Drag the revenue code dimension over expression horizontally

go to Presentation tab-> check Partial sum option for [Revenue code]