15 Replies Latest reply: Sep 29, 2011 7:13 PM by Stefan Wühl RSS

    Set Analysis: How to exclude sets from excluded sets

    Joe Kirwan

      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?

        • Set Analysis: How to exclude sets from excluded sets
          Stefan Wühl

          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
              Joe Kirwan

              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
                Joe Kirwan

                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
                    Stefan Wühl

                    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
                        Joe Kirwan

                        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
                          Joe Kirwan

                          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
                              Stefan Wühl

                              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
                                  Joe Kirwan

                                  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
                                      Stefan Wühl

                                      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:

                                      ITEMCODES1.png

                                       

                                      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
                                          Joe Kirwan

                                          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
                                              Stefan Wühl

                                              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
                                                  Joe Kirwan

                                                  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
                                                      Stefan Wühl

                                                      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
                                                          Stefan Wühl

                                                          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