Sum Distinct ID with Condition

[lzzoe]

Hi All,

I have quite new to Qlik. I want to create 4 fields (SUM_1, SUM_2, SUM_3, SUM_4) to count distinct ID meets criteria by category. May I ask how to create 4 expressions in front end.

Criteria:

SUM_1	Criteria1=1; Criteria2 = 2
SUM_2	Criteria1=2; Criteria2 = 1
SUM_3	Criteria1=1; Criteria2 = 1
SUM_4	Criteria1=2; Criteria2 = 2

Existing table:

ID	Category	Criteria1	Criteria2
432175	CateA	1	2
135354	CateA	1	1
647587	CateB	1	2
154324	CateB	1	2
867599	CateA	1	1
245654	CateA	1	1
589799	CateB	2	1
546762	CateA	2	1

Target Table:

Category	SUM_1	SUM_2	SUM_3	SUM_4
CateA	1	1	3	0
CateB	2	1	0	0

[hanna_choi]

Hello @lzzoe 

I have query about that
 - SUM_1 condition and SUM_3 condition have the same criteria condition.

 - SUM_2 condition and SUM_4 condition have the same criteria condition.

How are SUM_1 and SUM_3 conditions, SUM_2 and SUM_4 conditions distinguished?

[lzzoe]

Hi Hanna, (SUM_1, SUM_3)  and (SUM_2, SUM_4) are different in criteria2.

[hanna_choi]

Hello @lzzoe 

How about this way?

Step 1. Modeling

    

Step 2, Visualization

Use the Sum Functions.

[marcus_sommer]

You may try it in the UI with set analysis conditions, like:

count({< Criteria1={1}; Criteria2 = {2}>} distinct ID)