3 Replies Latest reply: May 15, 2014 10:14 AM by Grant Perkins RSS

    Compound Filter

    JLain _

      Well, I thought I could get the results I needed from a compound filter but it is not working. Help.

       

      I'm looking at a cash production report which pulls in invoices that were cleared and those items which are unapplied sitting on account. The problem with my SAP extract is that is giving me historical unapplied items...to report specifically what came in during a given month I need to cull out the aged unapplied items. The two filters I built independently perform well...in a compound situation I'm not getting the aged unapplied items removed.

       

      Filter 1:   .NotIn.("zz") (Filter title is "ATB without ZZ")

      This gives me every invoice that was cleared and any remaining items that remain unapplied.

      Filter 2: .NotIn.("rv","zz") .And. (Month()=11) (Filter title is "Doc Type not RV (INV)")

      This gives me every item that has not been applied to a particular invoice and is an item that was posted in the current month.

       

      The compound of these:

      OR

      All of the components when selected provides the data in filter 2. Any of the filters when selected gives me data from filter 1

        • Compound Filter
          Olly Bond

          Hello JLain,

           

          Your profile says you're on v8 - is that still right? I think the way you say Monarch is behaving makes sense, but I can't post a Venn diagram here to show how the intersections of the conditions apply

           

          Filter 1 shows you all your data that isn't DocType "ZZ". Filter 2 is just showing you a subset of filter 1 - it's everything that isn't "ZZ" and also isn't "RV" and also isn't from Month "11".

           

          Compounding filters where one is the subset of the other will give you the results you are getting.

           

          What happens when you edit Filter 1 to be: DocType<>"ZZ" .and. (Month()<>11) ? Do the results make more sense?

           

          Watch out if the variant in SAP that gives you the report includes data from previous years. You might end up including last November's unpaid invoices in this year's report...

           

          Best wishes

           

          Olly

            • Compound Filter
              JLain _

              Olly

              It was worth a try but not the results are farther off with your compound suggestion. Agreed on the alert for the results from SAP...first experience with it and it has been less than fun.

               

              I realize I'm close and I'm 98% accurate at the moment...however, from a reporting perspective, that is not close enough...I was thinking filtering may not be the way to go, but I dont have any good ideas for a calculated field either

                • Compound Filter
                  Grant Perkins

                  As a rule I always think it's good to make filter explicit. They are easier to understand that way.

                   

                  So I would tend to use "=" wherever possible rather than "not.in", especially when compounding. Even "In" and prepare a list of valid values might be more consistent in the longer term. And easier to test after any surprise changes that might come by to add to your day!

                   

                  In concept I would be looking to firstly get all the entries that are of interest, which seems to be your second filter but without the date selection. Then part two would sub-select from that for a specific document type or types and date restriction(s). Which would make it an AND compound rather than an OR  - at least in concept.

                   

                  I think the key to comppind filter that seem problematic is to break them right down to single selection criteria per filter whilst developing the formulas required. Remember that a compound filter can be built from other compound filters. Once you have the core multiselection filters working as you need them to work they should give you the formulas you need for a base level compound filter and lower levels can be deleted. (However if there are few lower levels and no performance issues then keeping the underlying toolkit may be a wise move for future maintenance or even the development of other, similar, filters for future requirements. I might look clumsy but should be an effective approach.)

                   

                   

                  Grant