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 · Accepted solution]

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

[mazacini]

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

[swuehl]

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

[mazacini]

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

[swuehl]

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.

[swuehl]

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