Set Analysis: How to exclude sets from excluded sets

[mazacini]

Hi

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?

[swuehl]

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

[mazacini]

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

[mazacini]

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

[swuehl]

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

[mazacini]

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

[mazacini]

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

[mazacini]

File attached

[swuehl]

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

[mazacini]

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