Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

3 Replies
Highlighted

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

View solution in original post

Highlighted
Champion II
Champion II

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

Highlighted
Contributor III
Contributor III

Ruben

Thank you so much~ It works in my apps!

Very appreciate your kind help~

Ivan