Need to get the distinct count of sk from both expressions for only matches sks

[AK9934]

1) get the distinct count of sk where type= AG

2) get the distinct count of sk where type= AGH

3) get the sk which are in step 1 not in step 2 

4) calculate distinct count of sk from step 3.

Please provide me the solution how to achieve this using set expression.

[lennart_mo]

Hi @AK9934,

Could you please elaborate on your data structure?
Are the types in step 1 and step 2 in the different fields or can one sk have multiple types?

[AK9934]

Hi @lennart_mo,

fact table contains Type and sk Columns.

Type is a column have only 2 values AG and AGH.

one sk may have both AG and AGH values.

I need to count of district of sk values which are common in both.

[Kushal_Chawda]

@AK9934  try below expression\

Count({<SK=p({1<Type={'AGH'}>})*p({1<Type={'AG'}>})>}distinct SK)

[AK9934]

Not working the above expression.

[Kushal_Chawda]

@AK9934  This should work. Make sure that you are using actual field name in your expression. If it still doesn't work, please share a sample data with expected output

[AK9934]

Hi,

For example if we take these 2 values in Excel like Column A has AG values and Column B has AGH Values, if we apply vlookup as vlookup(B2, A2:A77,1,0) then getting some are matches and other as N/A in Excel and  if we filter N/A in the lookup column then what are the values showing under column A I need in qliksense.

Can anyone please help?

[simsa]

Hi @AK9934,

I have attached the sample data in snippet that I have used to validate your requirement.

Here is the formula I have used, hope it works for you.

SUM(IF(AGGR(CONCAT(DISTINCT Category),ID)='A',1,0))

In your case, you can use:

SUM(IF(AGGR(CONCAT(DISTINCT type),sk)='AG',1,0))