Set analysis with multiple set modifiers with aggregation functions: {"=sum(x)>N"}

[Anonymous]

Dear fellow QV enthusiasts.

I need to add two conditions to set analysis that will work as AND:

1. Show only [Material Groups] that have > 1 order types

2. Show only [Order types] that have > 3 orders

The original table is like this, without filtering:

I can use set analysis in the expression for [# of associated POs] to get [Material Groups] that have > 1 order types.

=count(

{$

<

[Material Group] = {"=count(distinct [Order Type]) > 1"}

>

} distinct [Order Nr])

It works:

Now I need to get something like this, to show only [Order types] = F0 and ZSC1 and only [Material Group] = A002000:

I tried this:

=count(

{$

<

[Material Group] = {"=count(distinct [Order Type]) > 1"}

*

{"=count(distinct [Order Nr]) > 3"}

>

}

distinct [Order Nr]) 

or this:

=count(

{$

<

[Material Group] = {"=count(distinct [Order Type]) > 1"}

*

{"=count(distinct total <[Material Group],[Order Type]> [Order Nr]) > 3"}

>

}

distinct [Order Nr]) 

And it is not filtering:

[MK_QSL]

Can you provide your 10-15 lines sample data in excel file along with expected output?

[Anonymous]

There you go Manish.

Expected output should be like this:

only highlighted line should appear, except for Order type ZDL1 (where # of POs = 1).

[MK_QSL]

Why only Highlighted in Yellow?

Can you share the logic with an example from above screenshot?

[Anonymous]

The above screenshot with highlight is a mockup.

I need to add two conditions to set analysis that will work together as AND:

1. Show only [Material Groups] that have > 1 order types

2. Show only [Order types] that have > 3 orders

[ramoncova06]

sorry but was there really a need for such a big font ?

you issue seems simple ... wish you luck on getting it to work

[Anonymous]

Thanks for the comment Ramon. I pasted the text from mobile browser, and it didn't handle font size properly.

If you say the thing seems simple, why not trying to help me tackle it?

[ramoncova06]

ok get the mobile browser issue, though I would suggest editing the comment, several of the folks here are not Qlik employees and are just doing this to help and to expand our own knowledge.

your issue is with the <> you need to have an opening and closing one for each set you what to define in you intersection

=count(

{$

<[Material Group] = {"=count(distinct [Order Type]) > 1"}>

*

<[Order Type] = {"=count(distinct [Order Nr]) > 3"}>

>

}

distinct [Order Nr])

[MK_QSL]

Why I ask because the Sample File is not having that many orders so the output should be different for this excel file compare to the screenshot you have provided..

If you are not agree with me.. let me know what wrong I have asked !

[Anonymous]

Unfortunately, the set analysis condition you provided still does not work for me. I even did this on the sample file that I uploaded above.

Below a screenshot.

The first column has the formula

count(
{
<[Order Type] = {"=count(distinct total <[Material Group],[Order Type]>  [Order Nr]) >= 3"}>
*
<[Material Group] = {"=count(distinct [Order Type]) > 1"}>
}
distinct [Order Nr])

So the first column should display numbers only when the set analysis criteria are fulfilled, and otherwise display 0.

As you see, in Material Group A002000 (the one at the bottom), there is 0 for Order Type ZDL1. This is because the count of orders of type ZDL1 in material group A002000 is less than 3.

However, for Material Group A001000 and Order Type FO (the one at the top), there should also be 0 in the first column, because there is only 1 order of type FO in material group A001000. But the formula is displaying 1 instead.

For reference, I provide the formula from inside the set analysis modifier in columns 2 and 3.

I have the sample QlikSense file (based on the xls from the posts above) attached. To view it, please put it into C:\Users\<youruser>\Documents\Qlik\Sense\Apps\