Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
datagrrl
Creator III
Creator III

Percent of Total in Pivot Table

Hello-

I like the Pivot Table functionality because it allows me to drill through to the details in another table. A straight table allows that, but not as well in my example. Mainly because I need two dimensions.

I have included in the attached doc some options.

In the end I want a table that looks like this. I can re-model the data if needed so I am open to any suggestions.

   

YesNoTotal%Yes
Red50166676%
Orange576211948%
Black30184863%
Green983813672%

When I click on the 16 for No/Red I would like to just see those 16 detail records. While I can get a straight table to look the way I want, I can not get it to select the data I want (Because I am using Set Analysis instead of the Dimension as a Dimension in the chart)

1 Solution

Accepted Solutions
sunny_talwar

Check out the attached

Script:

LOAD Color,

     Sentiment,

     ID

FROM

Percent.xlsx

(ooxml, embedded labels, table is Data);

LOAD * Inline [

DIM

1

2

3

];

Pivot Table

Dimensions

1) Color

2) =Pick(DIM, Sentiment, 'Total', 'Percent')

Expression

=Pick(DIM, Count(ID), Count(ID), Num(Count({$<Sentiment={'Yes'}>}ID)/Count(ID), '##.%'))

Sort Expression for 2nd dimension

=Match(Pick(DIM, Sentiment, 'Total', 'Percent'), 'Yes', 'No', 'Total', 'Percent')

View solution in original post

11 Replies
sunny_talwar

Check out the attached

Script:

LOAD Color,

     Sentiment,

     ID

FROM

Percent.xlsx

(ooxml, embedded labels, table is Data);

LOAD * Inline [

DIM

1

2

3

];

Pivot Table

Dimensions

1) Color

2) =Pick(DIM, Sentiment, 'Total', 'Percent')

Expression

=Pick(DIM, Count(ID), Count(ID), Num(Count({$<Sentiment={'Yes'}>}ID)/Count(ID), '##.%'))

Sort Expression for 2nd dimension

=Match(Pick(DIM, Sentiment, 'Total', 'Percent'), 'Yes', 'No', 'Total', 'Percent')

datagrrl
Creator III
Creator III
Author

Thanks, that works well.

Shubham_Deshmukh
Specialist
Specialist

where is your Percent.xlsx. I am not getting this without it.

sunny_talwar

Here you go

Note: I just exported the table box with the three fields and saved it as Percent.xlsx

Shubham_Deshmukh
Specialist
Specialist

This pivoting I know Sunny. We can do it only in Pivot table. But using Pivot, it is not possible every time for merging like below,

Untitled.png

What is best practice in that case?(Suggestions) - Splitting Col 2,Col 3 in different report?

sunny_talwar

If you can provide some mock up data (in Excel) and provide the expected output, I might be able to help you out

Shubham_Deshmukh
Specialist
Specialist

See I have raw data(Can't share) and  one date on which date dimension is dependent. I want your suggestion on the scenario.

IMG 1 : Complete report

Untitled.png

Now, I know using Pivot we can create report like IMG 2

Untitled.png

But, in my report there is extra part like (IMG 3 below) with IMG 2 part which I think I can't append with IMG 2 in PIVOT Table.

Untitled.png

Kindly suggest me about the approch

1> Can we create such report in same table?

2> Is it standard way to split the report(IMG 1) into 2 reports (IMG 2, IMG 3)?Because by splitting them I think we can develop it.

sunny_talwar

I am unable to load images, would you be able to post the Excel file for me

Uploading a Sample

Shubham_Deshmukh
Specialist
Specialist

Ok let me start a discussion.