topic Re: Set Analysis: How to exclude sets from excluded sets in QlikView

Set Analysis: How to exclude sets from excluded sets

mazacini — Sun, 25 Sep 2011 15:04:28 GMT

My app contains an ITEM table containing ITEMCODE, TARGET (2 values 'y' and 'n') and CATEGORY, and a TRANS table containing DATE, MONTH, REF, ITEMCODE and QTY.

I created a PIVOT table showing SUM(QTY) per MONTH for each ITEMCODE.

I then apply filters, selecting fields from TARGET and CATEGORY.

I want to create a STRAIGHT table showing the ITEMCODES not selected, but only for the selected CATEGORY (if selected), and only for a TARGET value 'y'.

Any ideas?

Set Analysis: How to exclude sets from excluded sets

swuehl — Sun, 25 Sep 2011 17:12:57 GMT

Hope I got you correctly, I think you could try something like

=sum({<ITEMCODE = e(ITEMCODE), TARGET={y}>} QTY)

as expression (summing the QTY is just to get a result, you could also sum just 1. Check that suppress zero values option in presentation tab is enabled).

This should return you the ITEMCODES which are currently excluded by your selection and where TARGET = 'y'

Regards,

Stefan

Set Analysis: How to exclude sets from excluded sets

mazacini — Sun, 25 Sep 2011 20:31:41 GMT

Thank you Stefan

I can see where you are going,

It is not working for me at the moment. But I see I may havve some anomalies in my load - I will eliminate these and retry your solution later.

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Mon, 26 Sep 2011 11:27:34 GMT

Hi Stefan

I think your formula works where I select TARGET = y, and then select on an ITEMCODE, or a number of ITEMCODES.

Thank you very much.

However, it ONLY seems to work with the combination of TARGET = y, and an ITEMCODE selection.

Whenever I use other filters, with or instead of ITEMCODE filter, it does not work for me.

I have taken the liberty of attaching my table structure.

My problem:

If I select Target = "y", my Pivot will show sales of all target ITEMCODE per month.

If I then select, say, ACCCODE or SUPPNAME, or a combination of both, this will filter my Pivot report accordingly.

I want my straight table to show what ITEMCODES with Target ="y" for that SUPPNAME have NOT been sold to that ACCCODE.

So I need to be able to show the excluded ITEMCODES in my straight table, but to filter the excluded items by the same filters as apply to my Pivot Table.

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Mon, 26 Sep 2011 20:34:14 GMT

You are filtering your ITEMCODES by selecting on SUPPNAME? I believe you have added some kind of select in field logic to your app, since ITEMCODE is not linked to SUPPNAME, right?

I do have some vague ideas of how you might do the filtering, but it would be much easier for me or someone else if you could provide a small sample app representing your real data model. Would this be feasible?

Regards,

Stefan

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Mon, 26 Sep 2011 23:32:55 GMT

Hi Stefan

Yes it is feasible. Thank you for persisting with this. It will take me a little while to generate a sample version of my model. It is after midnight here, so I will not have it until morning.

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Mon, 26 Sep 2011 23:57:04 GMT

Hi Stefan

Instead of waiting till morning, I have pulled together the sample app as attached.

I want to identify the Target ITEMCODES sold (Items Sold Chart) as well as those not sold (Items Not Sold)

So the Target will always be selected as 'y' for the purposes of this analysis.

If I select Owner, it will show Target ITEMCODES sold for that Owner (Owner owns customer ACCCODEs). Items Not Sold should show Target Items Not Sold to that customer ACCCODE.

If I add a SUPPCODE filter, Items Sold will show Target ITEMCODES from that SUPPCODE sold to customer ACCCODE. Items Not Sold should then show Target ITEMCODES FOR THAT SUPPCODE not sold to that customer ACCCODE.

I hope you understand, as I am starting to confuse myself.

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Tue, 27 Sep 2011 00:02:46 GMT

File attached

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Wed, 28 Sep 2011 17:32:50 GMT

Hi Joe,

I noticed that you used TARGET as field name in your set expression for not sold ITEMCODES, where I think it should be Target instead (field names are case sensitive).

I am not sure if I understood your business requirement correctly, but I think with this change, things look much different and are IMHO may correct.

I added a table with three expression

1) gimme all distinct ITEMCODES: 3100

2) gimme all distinct ITEMCODES within the current selection (IMHO, that's the ITEMCODES for your sales table)

With all three owners, you have 596 ITEMCODES sold, and 326 to owner COLM only.

3) gimme all distinct ITEMCODES exluded in my current selection, but with target y (with expression as given above).

I get zero ITEMCODES for all owners, and 270 for owner COLM only.

--> Regardless of what selection I perform on the left side, the sum of 2) and 3) is always 596. To me, that looks correct.

Maybe I missed a point, if so, please indicate by giving an example.

Regards,

Stefan

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Wed, 28 Sep 2011 17:45:47 GMT

Hi Stefan

Thanks for response.

Unfortunatley, I only have PE.

Maybe you can you post expressions on this discussion, and maybe send image of table?

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Wed, 28 Sep 2011 18:16:57 GMT

Ok,

I used a straight table chart, no dimensions, then 4 expressions:

=count({1} distinct ITEMCODE)

=count(distinct ITEMCODE)

=count({1<Target= {y}, ITEMCODE=e(ITEMCODE)>} DISTINCT ITEMCODE)

=column(2)+column(3)

Please note the Target instead of TARGET in your original app.

This is how it looks like:

You will notice that on the upper right chart, I get 271 rows instead of 270, that's because one ITEMCODE has two DESC: 05605

Hope this helps,

Stefan

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Wed, 28 Sep 2011 20:34:13 GMT

Hi Stefan

That's just brilliant!

Am I right in thinking that the new table is just to verify the Sold / Not Sold tables?

Also (Last thing!!!)

If I select on a SUPPCODE, is there a way that the Items Not Sold will show the Target = y items not sold, BUT ONLY FOR THAT SUPPCODE?

Thanks for your excellent support so far. I'll understand if you have exhaused your patience on this discussion!

Rgds

Joe

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Wed, 28 Sep 2011 21:36:16 GMT

Hi Joe,

try this

=sum({1<Target= {y},ITEMCODE= e(ITEMCODE),SUPPCODE=p(SUPPCODE) >} 1)

as expression in your table.

Not sure if this is correct, because I wasn't able to set up a verification table like before (it's getting late).

But I think it should be something like that.

Regards,

Stefan

Re: Set Analysis: How to exclude sets from excluded sets

mazacini — Thu, 29 Sep 2011 07:43:48 GMT

Hi Stefan

I don't know if you are familiar with the term Hitchhiker Syndrome. It describes a situation were you pick up a hitchhiker who , instead of being grateful, increasingly requests you divert further away from your route, and closer to their destination.

Well, I think I may be that hitchhiker!

Anyway, the last expression works, BUT ONLY WHEN SUPPCODE IS SELECTED.

Is there some way I can amend the expression to read SUPPCODE=p(SUPPCODE), if SUPPCODE is selected?

I think it might work then?

Joe

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Thu, 29 Sep 2011 08:22:37 GMT

Hi Hitchhiker,

I may need to think for awhile how to include that in a set expression.

Almost for sure this should work

=if(GetSelectedCount( SUPPCODE ) >0 , ExpressionWithSUPPCODE=p(SUPPCODE), ExpressionWithout) )

Regards,

Stefan

edit: You might also try

=sum({1<Target= {y},ITEMCODE= e(ITEMCODE),SUPPCODE= SUPPCODE >} 1)

(not using the if(...), not using p()) this should assign only actively selected SUPPCODEs to SUPPCODE within our set expression).

Haven't tried this myself, but maybe this is what you are looking for.

Re: Set Analysis: How to exclude sets from excluded sets

swuehl — Thu, 29 Sep 2011 23:13:54 GMT

Ok, after some more driving around aorund, I think you should change the expression in your NOT SOLD, but SUPPCODE table at the upper right corner to

=count({1<Target= {y},ITEMCODE= e(ITEMCODE),SUPPCODE=p(SUPPCODE) >} DISTINCT ITEMCODE)

(it's importan to count the ITEMCODE here, not just sum (1) )

This looks better to me. I noticed that there are some ITEMCODE without a SUPPCODE, like 1B173 and 1B277, so

count({1<Target= {y},ITEMCODE= e(ITEMCODE) >} DISTINCT ITEMCODE)

will give you some more ITEMCODES than first expression.

Hope this helps,

Stefan