Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mr_small_t
Contributor III
Contributor III

A set analysis with an if statment with a set analysis within??

Hi,

I have a problem using set-analysis. 

In a pivot table with SKOLENR as dimension i have 2 expressions that works:

Expression 1:

=count(DISTINCT{1<
GRLID = {"20200303ex"},
ONSKENR = {1},
SKOLENR = {">=98 <=104"}
>
} Rnr)

Expression 2:

=count(DISTINCT{1<
GRLID = {"20200303ex"},
ONSKENR = {1},
SKOLENR = {">=98 <=104"},
KOMMNR = {">=1400 <=1499"}
>
} KOMMNR)

So far so good. and I for example get that it is 57 Rnr at SKOLENR 98, and 7 KOMMNR at the same SKOLENR.

Then the problem starts. For each SKOLENR I want to now how many KOMMNR there is where the number of Rnr is greater than 5.

I hope someone can help me, even if it is summer!

Regards,

Torbjørn 

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

I guess, count for 103 would be zero. Try expression like:

tresesco_0-1594284501311.png

=Count(Distinct if(Aggr(
count(DISTINCT{1<
GRLID = {"20200303ex"},
ONSKENR = {1},
SKOLENR = {">=98 <=104"}
>
} Rnr)
,KOMMNR,SKOLENR)>5, KOMMNR))

View solution in original post

6 Replies
tresesco
MVP
MVP


Then the problem starts. For each SKOLENR I want to now how many KOMMNR there is where the number of Rnr is greater than 5.


May be something like,

=count(DISTINCT{1<KOMMNR = {"=Count(Distinct Rnr)>5"}>} KOMMNR)

Mr_small_t
Contributor III
Contributor III
Author

Hi

thanks for your reply. It is much appreciated. But it does not entirely solve my problem as I far as I can see. Using your suggestion in an expression 2 above, gives me:

=count(DISTINCT{1<
GRLID = {"20200303ex"},
ONSKENR = {1},
SKOLENR = {">=98 <=104"},
KOMMNR = {">=1400 <=1499", "=Count(Distinct Rnr)>5"}
>
} KOMMNR)

But i need to inlude the set analysis as in Expression 1 above in the "=Count(Distinct Rnr)>5)"-statement. I tried to include Expression 1 between the "" in your suggestion, but i could not get it to work. Any suggestion?

 

tresesco
MVP
MVP

Try to share a sample app and explain the expected output in that context, will give a try.

Mr_small_t
Contributor III
Contributor III
Author

Hi, attached is a sample app and a dataset. In the QW example I have written in an red textbox what i want as answer.

I hope this help!

T

 

 

tresesco
MVP
MVP

I guess, count for 103 would be zero. Try expression like:

tresesco_0-1594284501311.png

=Count(Distinct if(Aggr(
count(DISTINCT{1<
GRLID = {"20200303ex"},
ONSKENR = {1},
SKOLENR = {">=98 <=104"}
>
} Rnr)
,KOMMNR,SKOLENR)>5, KOMMNR))

Mr_small_t
Contributor III
Contributor III
Author

Hi, my bad. Yes, you are correct that the count for 103 should be 0.

This works perfect! Thank you for your quick and correct reply! 

Regards,

Torbjørn