Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
New-Qlik
Creator II
Creator II

Pivot Table Functionality

Hello everyone

I want to know weather below thing is possible

   

Customerc1c2
count%count%
Product
P1100.166667200.222222
P2200.333333300.333333
P3300.5400.444444
Total601901
Cancelled 200.333333400.444444
total -cancelled40 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

1 Solution

Accepted Solutions
sunny_talwar

This?

Capture.PNG

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

1

2

3

4

];

Pivot Table

Dimensions:

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

Customer

Expressions:

=Pick(Dim,

Sum(Orders),

Sum(Orders),

Sum(Cancelled),

Sum(Orders) - Sum(Cancelled))

Pick(Dim,

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

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

View solution in original post

18 Replies
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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?

New-Qlik
Creator II
Creator II
Author

Thanks for instant reply..

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

it is coming as below

   

Customerc1c2
count%Cancelledcount%Cancelled
Product
P1100.1666675200.22222210
P2200.3333335300.33333310
P3300.510400.44444410
Total601 901
New-Qlik
Creator II
Creator II
Author

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

   

ProductCutomerOrdersCancelled
P1C1105
P2C1205
P3C13010
P1C22010
P2C23020
P3C24010
sunny_talwar

This?

Capture.PNG

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

1

2

3

4

];

Pivot Table

Dimensions:

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

Customer

Expressions:

=Pick(Dim,

Sum(Orders),

Sum(Orders),

Sum(Cancelled),

Sum(Orders) - Sum(Cancelled))

Pick(Dim,

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

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

raju_insights
Partner - Creator III
Partner - Creator III

Hi avneet,

PFA

Pivot.JPG

New-Qlik
Creator II
Creator II
Author

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

sunny_talwar

Did you look at the solution I provided?

New-Qlik
Creator II
Creator II
Author

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