topic Re: Percent of Total in Pivot Table in QlikView

Percent of Total in Pivot Table

datagrrl — Mon, 09 May 2016 11:54:11 GMT

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.

	Yes	No	Total	%Yes
Red	50	16	66	76%
Orange	57	62	119	48%
Black	30	18	48	63%
Green	98	38	136	72%

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)

Re: Percent of Total in Pivot Table

sunny_talwar — Mon, 09 May 2016 12:01:13 GMT

Check out the attached

Script:

LOAD Color,

Sentiment,

FROM

Percent.xlsx

(ooxml, embedded labels, table is Data);

LOAD * Inline [

DIM

];

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')

Re: Percent of Total in Pivot Table

datagrrl — Mon, 09 May 2016 12:03:40 GMT

Thanks, that works well.

Re: Percent of Total in Pivot Table

Shubham_Deshmukh — Tue, 03 Apr 2018 09:19:09 GMT

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

Re: Percent of Total in Pivot Table

sunny_talwar — Tue, 03 Apr 2018 11:22:21 GMT

Here you go

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

Re: Percent of Total in Pivot Table

Shubham_Deshmukh — Tue, 03 Apr 2018 12:38:36 GMT

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,

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

Re: Percent of Total in Pivot Table

sunny_talwar — Tue, 03 Apr 2018 12:39:37 GMT

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

Re: Percent of Total in Pivot Table

Shubham_Deshmukh — Tue, 03 Apr 2018 12:58:44 GMT

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

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

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.

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.

Re: Percent of Total in Pivot Table

sunny_talwar — Tue, 03 Apr 2018 13:03:26 GMT

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

Uploading a Sample

Re: Percent of Total in Pivot Table

Shubham_Deshmukh — Tue, 03 Apr 2018 13:05:48 GMT

Ok let me start a discussion.

Re: Percent of Total in Pivot Table

sunny_talwar — Tue, 03 Apr 2018 13:07:59 GMT

Sure

Re: Percent of Total in Pivot Table

Shubham_Deshmukh — Tue, 03 Apr 2018 13:22:53 GMT

Append columns with merged columns in PIVOT Table

Re: Percent of Total in Pivot Table

RanOuerg — Fri, 19 Apr 2024 12:50:24 GMT

@Shubham_Deshmukh did you find the solution for question, i have the same issue