topic Re: A set analysis with an if statment with a set analysis within?? in QlikView

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

Mr_small_t — Sat, 16 Nov 2024 00:21:23 GMT

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

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

tresB — Thu, 09 Jul 2020 05:56:39 GMT

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)

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

Mr_small_t — Thu, 09 Jul 2020 06:57:48 GMT

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?

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

tresB — Thu, 09 Jul 2020 07:27:12 GMT

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

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

Mr_small_t — Thu, 09 Jul 2020 08:10:04 GMT

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!

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

tresB — Thu, 09 Jul 2020 08:48:40 GMT

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

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

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

Mr_small_t — Thu, 09 Jul 2020 08:50:57 GMT

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