How to Group Detractors in Different Columns?

[ivanyang25]

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’

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. 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

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

[rubenmarin · Accepted solution]

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]

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

[ivanyang25]

Ruben

Thank you so much~ It works in my apps!

Very appreciate your kind help~

Ivan