Pivot Table Functionality

[New-Qlik]

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

[New-Qlik]

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

[sunny_talwar]

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

Dim:

LOAD * Inline [

Dim

1

2

3

4

];

and then try the dimensions and expressions I have mentioned above

[rajuamet]

Hi Avneet,

use this

=

If(Product='Total',3,

If(Product='Cancelled',2,

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

and sort descending.

[New-Qlik]

Its Working Perfect.

Both the solutions are working

[sunny_talwar]

Both? Mine and raju_insights‌'s solution?

[New-Qlik]

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

Its easy:)

[New-Qlik]

HI

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

i.e.

Sum(Cancelled),

Sum(Orders) - Sum(Cancelled))

[sunny_talwar]

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

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

[New-Qlik]

Thanks Sunny, its working.