topic Re: Pivot Table Functionality in QlikView

Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 11:09:46 GMT

Hello everyone

I want to know weather below thing is possible

Customer	c1		c2
	count	%	count	%
Product
P1	10	0.166667	20	0.222222
P2	20	0.333333	30	0.333333
P3	30	0.5	40	0.444444
Total	60	1	90	1
Cancelled	20	0.333333	40	0.444444
total -cancelled	40		50

Database column are Product, Customer, Orders, cancelled Order

our dimensiona re: Product and Customer

expression is 1. count = Sum of sales

2. % = sum(sales)/sum(TOTAL<cutomer>)Sales

so now I have to add cancelled and (total-cancelled) in below

How can we do it in pivot table and what is the option to implement same layout.

TIA

Re: Pivot Table Functionality

vinieme12 — Thu, 04 Aug 2016 11:15:18 GMT

just Drag and Drop your Customer Dimension over the Expressions, that's all you need to do.

when you drag the Dimension, you will see a Blue line that shows you how the dimension will be placed for guidance

Re: Pivot Table Functionality

sunny_talwar — Thu, 04 Aug 2016 11:17:53 GMT

You might be able to get this through using Synthetic dimensions or using an Island table in your pivot table. Would you be able to provide the raw data behind this so that we can show you how this might be done?

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 11:22:55 GMT

Thanks for instant reply..

I did same for customer, But my issue is cancelled should come down after total

it is coming as below

Customer	c1			c2
	count	%	Cancelled	count	%	Cancelled
Product
P1	10	0.166667	5	20	0.222222	10
P2	20	0.333333	5	30	0.333333	10
P3	30	0.5	10	40	0.444444	10
Total	60	1		90	1

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 11:25:29 GMT

Thanks Sunny, Really no idea about what u have mentioned. Please guide below is my sample data

Product	Cutomer	Orders	Cancelled
P1	C1	10	5
P2	C1	20	5
P3	C1	30	10
P1	C2	20	10
P2	C2	30	20
P3	C2	40	10

Re: Pivot Table Functionality

sunny_talwar — Thu, 04 Aug 2016 12:26:35 GMT

This?

Script:

Table:

LOAD Product,

Cutomer,

Orders,

Cancelled

FROM

[https://community.qlik.com/thread/227909]

(html, codepage is 1252, embedded labels, table is @3);

Dim:

LOAD * Inline [

Dim

];

Pivot Table

Dimensions:

=Pick(Dim, Product, 'Total', 'Cancelled', 'Total - Cancelled')

Customer

Expressions:

=Pick(Dim,

Sum(Orders),

Sum(Cancelled),

Sum(Orders) - Sum(Cancelled))

Pick(Dim,

Sum(Orders)/Sum(TOTAL <Cutomer> Orders),

Sum(Cancelled)/Sum(TOTAL <Cutomer> Orders),

(Sum(Orders) - Sum(Cancelled))/Sum(TOTAL <Cutomer> Orders))

Sorting the 1st Dimension by a sorting expression: Dim

Re: Pivot Table Functionality

rajuamet — Thu, 04 Aug 2016 12:43:59 GMT

Hi avneet,

PFA

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 13:04:39 GMT

Hi,

I can write this expression n

If(Product='P1',1,

If(Product='P2',2,

If(Product='P3',3,

If(Product='Total',4,

If(Product='Cancelled',5,

If(Product='Total - Cancelled',6,))))))

because this will be hardcoding ass my products will get change and are many:(

is there anything else I can dp

Re: Pivot Table Functionality

sunny_talwar — Thu, 04 Aug 2016 13:08:19 GMT

Did you look at the solution I provided?

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 13:09:37 GMT

Hi,

Thanks but I have one doubt.

Product are coming from ProductMaster table

Customer -->customer Master table

Orders and cancelled are from different table

all these tables are join.

how do I implement your solution

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 13:12:20 GMT

Yes Sunny I did.

But I have one doubt.

Product are coming from ProductMaster table

Customer -->customer Master table

Orders and cancelled are from different table

all these tables are join.

how do I implement your solution

Re: Pivot Table Functionality

sunny_talwar — Thu, 04 Aug 2016 13:12:51 GMT

Don't need to touch anything in your existing data model. Just add this at the end of the script:

Dim:

LOAD * Inline [

Dim

];

and then try the dimensions and expressions I have mentioned above

Re: Pivot Table Functionality

rajuamet — Thu, 04 Aug 2016 13:13:29 GMT

Hi Avneet,

use this

If(Product='Total',3,

If(Product='Cancelled',2,

If(Product='Total - Cancelled',1,4)))

and sort descending.

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 14:03:51 GMT

Its Working Perfect.

Both the solutions are working

Re: Pivot Table Functionality

sunny_talwar — Thu, 04 Aug 2016 14:24:09 GMT

Both? Mine and raju_insights‌'s solution?

Re: Pivot Table Functionality

New-Qlik — Thu, 04 Aug 2016 18:02:40 GMT

Yes but since I don't want to do much in script level so used yours.

Its easy:)

Re: Pivot Table Functionality

New-Qlik — Mon, 29 Aug 2016 09:47:10 GMT

Is it Possible to change format for dim 3 , 4 to bold?

i.e.

Sum(Cancelled),

Sum(Orders) - Sum(Cancelled))

Re: Pivot Table Functionality

sunny_talwar — Mon, 29 Aug 2016 09:51:41 GMT

You can add a if statement for your text format to bold Dim 3 and 4

=If(Match(Dim, 3, 4), '<b>')

Re: Pivot Table Functionality

New-Qlik — Mon, 29 Aug 2016 11:11:38 GMT

Thanks Sunny, its working.