topic Re: Percentage in Pivot Table in QlikView

Percentage in Pivot Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am very new to Qlik and am making a pivot where i am trying to get the percentage of a particular column.In this column there is text eg : Auto , Manual, Semi_auto. Now i want to see what is the percentage of each. i am attaching the output with the error.

Please help me here.

Regards,

Rohit

Re: Percentage in Pivot Table

sunny_talwar — Mon, 30 Jul 2018 17:50:15 GMT

May be try this

Count(Field)/Count(TOTAL <Transaction_Mode> Field)

Re: Percentage in Pivot Table

Anonymous — Tue, 31 Jul 2018 07:36:51 GMT

Thanks for the reply Sunny but this is not working for me. when I type count(Auto)/Count(Total<Transaction_Mode>Auto) the formula does not work. Transaction Mode is a calculated Dimension. where is calculates Auto, Semi and manual basis few if conditions. Hope this help for giving me a solution.

if( CREATED_BY = MODIFIED_BY,'Manual',

IF( CREATED_BY = 'SABRE'and LEN(MODIFIED_BY)< 1, 'Auto',

if( CREATED_BY = 'GALILEO'and LEN(MODIFIED_BY)< 1, 'Auto',

if( CREATED_BY = 'AMADEUS'and LEN(MODIFIED_BY)< 1, 'Auto',

if( CREATED_BY = 'SERVICE'and LEN(MODIFIED_BY)< 1, 'Auto',

if( CREATED_BY = 'SER-AMA'and LEN(MODIFIED_BY)< 1, 'Auto',

IF( CREATED_BY = 'SABRE' and LEN(MODIFIED_BY)> 1,'Semi Auto',

if( CREATED_BY = 'GALILEO'and LEN(MODIFIED_BY)> 1,'Semi Auto',

if(CREATED_BY = 'AMADEUS'and LEN(MODIFIED_BY)> 1,'Semi Auto',

if( CREATED_BY = 'SER-AMA'and LEN(MODIFIED_BY)> 1,'Semi Auto',

if(CREATED_BY = 'SERVICE'and LEN(MODIFIED_BY)> 1,'Semi Auto',

IF( CREATED_BY <> 'SABRE' and LEN(CREATED_BY)> 1 and len(MODIFIED_BY)<1,'Manual',

if( CREATED_BY <> 'GALILEO'and LEN(CREATED_BY)> 1 and len(MODIFIED_BY)<1,'Manual',

if(CREATED_BY <> 'AMADEUS'and LEN(CREATED_BY)> 1 and len(MODIFIED_BY)<1,'Manual',

if( CREATED_BY <>'SER-AMA'and LEN(CREATED_BY)>1 and len(MODIFIED_BY)<1,'Manual',

if(CREATED_BY <> 'SERVICE'and LEN(CREATED_BY)> 1 and len(MODIFIED_BY)<1,'Manual','Manual')))))))))))))))))

Regards,

Rohit

Re: Percentage in Pivot Table

sunny_talwar — Tue, 31 Jul 2018 15:29:18 GMT

Would you be able to perform this calculation in the script to create a field?

Re: Percentage in Pivot Table

neelamsaroha157 — Tue, 31 Jul 2018 15:49:26 GMT

As suggested by Sunny, it would be easier to work on front end when its created in script.

One more suggestion- the if condition can be shortened with the 'Match' function as -

if( CREATED_BY = MODIFIED_BY,'Manual',

IF( Match(CREATED_BY , 'SABRE', 'GALILEO', 'AMADEUS', 'SERVICE', SER-AMA') and LEN(MODIFIED_BY)< 1, 'Auto',

IF( Match(CREATED_BY, 'SABRE' , 'GALILEO', 'AMADEUS', 'SERVICE', SER-AMA') and LEN(MODIFIED_BY)> 1,'Semi Auto',

IF( not Match(CREATED_BY , 'SABRE' , 'GALILEO', 'AMADEUS', 'SERVICE', SER-AMA') and LEN(CREATED_BY)> 1 and len(MODIFIED_BY)<1,'Manual',

,'Manual'))))

Confirm the number of closing and opening brackets.

Re: Percentage in Pivot Table

Anonymous — Wed, 01 Aug 2018 11:12:35 GMT

Hi Neelam thanks for the response but the condition you have given is showing error. Please help.

Re: Percentage in Pivot Table

Anonymous — Wed, 01 Aug 2018 11:17:59 GMT

Hi Sunny ,

when I am doing Count(Auto)/Count(Total <Transaction_Mode> Auto it is showing error

Re: Percentage in Pivot Table

neelamsaroha157 — Wed, 01 Aug 2018 15:25:47 GMT

What error is it, can you share a screenshot or sample file?

Re: Percentage in Pivot Table

sunny_talwar — Wed, 01 Aug 2018 15:25:58 GMT

Did you create Transaction_Mode in script first?