Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ivanyang25
New Contributor III

How to Group Detractors in Different Columns?

Dear Qlik Sense Experts

I am new to Qlik Sense and I am a front end user

I have 2 questions base on my client’s request and I want to do it by writing expression from front end


1. Sheet DA:  The detractors in Column A is: Late/Early/Make; The detractors in Column B is: Late/Early/Make

I want to group them as below

  1. When Column A is Make, then we group it as ‘Make’
  2. When Column A is Early, then we group it as ‘Early’
  3. When Column A is Late and Column B is Make, we group it as ‘M4 Fail’
  4. When Column A is Late and Column B is Early and Late, we group it as ‘FPSD Fail’

1.PNG

I try to write below express in dimension in master item, but it doesn’t work

=if (A='Late'and B= ‘Early’ and B=’Late’, ’FPSD Fail’

if (A='Late' and B='Make', 'M4 Fail',

if (A='Make','Make',

if (A='Early','Early'

))))

The reason I say it doesn’t work is that when I put a Pivot table to calculate the total order line group as ‘FPSD Fail’, the number doesn’t match raw data (which should be 7625)

2.PNG

2. Sheet OTD: The detractors in Column D is 0/1; The detractors in Column E is 0/1; The detractors in Column F is 0/1

I want to group them as below

  1. When Column D is ‘1’, then we group it as ‘Make’
  2. When Column D is ‘0’ and column E is ‘1’, then we group it as ‘M4 Fail
  3. When Column D is ‘0’ and column E is ‘0’ and column F is ‘1’, then we group it as ‘M1M2 Fail
  4. When Column D is ‘0’ and column E is ‘0’ and column F is ‘0’, then we group it as ‘M3 Fai

3.PNG

May I know how to make it happen by writing expression from front end

You could refer to raw data in attachment

Thank you for your advice in advance

Ivan

Tags (1)
1 Solution

Accepted Solutions

Re: How to Group Detractors in Different Columns?

Hi Ivan, about your first question, the or in the first line should be between parenthesys, otherwise it will count any record with B='Late' (not cheking 'A' value):

=if (A='Late'and (B='Early' or B='Late'),'FPSD Fail',

if (A='Late' and B='Make', 'M4 Fail',

if (A='Make','Make',

if (A='Early','Early'

))))

About the second, maybe:

If(D=1, 'Make',

If(E=1, 'M4 Fail',

   If(F=1, 'M1M2 fail', 'M3 Fail')))

3 Replies

Re: How to Group Detractors in Different Columns?

Hi Ivan, about your first question, the or in the first line should be between parenthesys, otherwise it will count any record with B='Late' (not cheking 'A' value):

=if (A='Late'and (B='Early' or B='Late'),'FPSD Fail',

if (A='Late' and B='Make', 'M4 Fail',

if (A='Make','Make',

if (A='Early','Early'

))))

About the second, maybe:

If(D=1, 'Make',

If(E=1, 'M4 Fail',

   If(F=1, 'M1M2 fail', 'M3 Fail')))

vinieme12
Esteemed Contributor II

Re: How to Group Detractors in Different Columns?

If you really need this grouping then i suggest you make this column in the script when loading the data

ivanyang25
New Contributor III

Re: How to Group Detractors in Different Columns?

Ruben

Thank you so much~ It works in my apps!

Very appreciate your kind help~

Ivan

Community Browser