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

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

Anonymous — Wed, 22 Apr 2015 15:59:23 GMT

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:

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

MK_QSL — Wed, 22 Apr 2015 16:14:21 GMT

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

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

Anonymous — Wed, 22 Apr 2015 16:24:27 GMT

There you go Manish.

Expected output should be like this:

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

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

MK_QSL — Wed, 22 Apr 2015 16:36:40 GMT

Why only Highlighted in Yellow?

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

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

Anonymous — Wed, 22 Apr 2015 18:07:44 GMT

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

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

ramoncova06 — Wed, 22 Apr 2015 18:21:22 GMT

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

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

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

Anonymous — Wed, 22 Apr 2015 18:26:16 GMT

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?

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

ramoncova06 — Wed, 22 Apr 2015 18:46:48 GMT

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])

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

MK_QSL — Wed, 22 Apr 2015 18:58:58 GMT

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 !

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

Anonymous — Wed, 22 Apr 2015 20:18:59 GMT

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\

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

Anonymous — Wed, 22 Apr 2015 21:13:34 GMT

I have provided a sample file, which can be used to reproduce the logic that I described above. It think the exact row count does not matter if we need to prove the concept.

In the attached dataset, I have 2 material groups, each containing 3 order types. One of the 2 material groups fulfills the set analysis criteria: it has more than 1 order types and >=3 orders in at least 2 order types. So, I'm sure this dataset is enough to illustrate the case. Just looking for the formula.

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

ramoncova06 — Thu, 23 Apr 2015 01:10:24 GMT

the issue is that we are using advance search to get the order type, and because in the material group A002000 it does have 3 entries it is included as a valid group for each material group

this is one option, is not clean, but will get you out of the problem

if(count(

{

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

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

}

distinct [Order Nr]) < 3, 0,count(

{

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

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

}

distinct [Order Nr]))

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

ramoncova06 — Thu, 23 Apr 2015 01:22:06 GMT

here is another option

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

and count([Order Type]) > 1,

count(distinct([Order Nr])),0

)