Percentage in Pivot Table

[Anonymous]

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

[sunny_talwar]

May be try this

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

[Anonymous]

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

[sunny_talwar]

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

[neelamsaroha157]

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.

[Anonymous]

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

[Anonymous]

Hi Sunny ,

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

[neelamsaroha157]

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

[sunny_talwar]

Did you create Transaction_Mode in script first?