5 Replies Latest reply: May 15, 2014 10:05 AM by adonis _ RSS

    Help with Summary Filter to exclude 0s

    adonis _

      Hi quick question,

      i am trying to filter data in a summary.

      I was able to group up the net qty and net amt in the measure area so that it shows me 0 and 0 values.

      the question is how can I create a filter that removes them.

      is it because it is in the measure field i can't remove it?

      please advise.

      I tried making formulas to remove them but am stuck.

      Net AMT Net QTY

      0     0

      -2000     -20

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      -90000     0

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      0     0

      -200000     -199500

      0     0

      -20000000     -19563022.86

      0     0

      -11000     -11448.37

      0     0

      0     0

      21643000     14926008.04

      0     490.9

      24450000     764160.59

      -12     0

      0     0

      0     0

      0     0

      -26449     0

      401     1162.9

      0     0

      0     0

      4700     47485.04

      0     0

      0     0

      0     0

      0     11304

        • Help with Summary Filter to exclude 0s
          adonis _

          Sec #     Net Qty     Net Amt           

          1     -100,000.00     -102,696.23          

          1     100,000.00     102,696.23     

          2     -2,000.00     -20          

          3     -25,000.00     -25,867.53          

          3     25,000.00     25,867.53          

          4     -25,000.00     -25,179.17          

          4     25,000.00     25,179.17     

          5     -37,000.00     -37,245.82     

          5     37,000.00     37,245.82          

          6     -7,000.00     -7,788.00     

          6     7,000.00     7,788.00          

          7     -236,000.00     -228,097.28          

          7     236,000.00     228,097.28     

          8     -1,000.00     -1,113.86     

          8     1,000.00     1,113.86          

          9     -90,000.00     0          

          10     -40,000.00     -43,637.50          

          10     40,000.00     43,637.50     

          11     -2,000.00     -2,049.74          

          11     2,000.00     2,049.74          

          12     -20,000.00     -21,588.41     

          12     20,000.00     21,588.41     

          13     -100,000.00     -98,270.00          

          13     100,000.00     98,270.00          

          14     -750,000.00     -812,041.67          

          14     750,000.00     812,041.67     

          15     -50,000.00     -49,400.00          

          15     50,000.00     49,400.00          

          16     -35,000.00     -34,562.50     

          16     35,000.00     34,562.50     

          17     -125,000.00     -122,500.00     

          17     125,000.00     122,500.00     

          18     -17,000,000.00     -17,053,492.36     

          18     17,000,000.00     17,053,492.36     

          19     -278,000.00     -275,678.70     

          19     278,000.00     275,678.70          

          20     -250,000.00     -249,390.00     

          21     50,000.00     49,890.00     

          22     -173,000.00     -173,000.00     

          22     173,000.00     173,000.00     

          23     -20,000,000.00     -19,563,022.86     

          24     -15,000.00     -15,121.95     

          24     15,000.00     15,121.95     

          25     -11,000.00     -11,448.37          

          26     -309,619.00     -304,484.97          

          26     309,619.00     304,484.97     

          27     -27,000,000.00     -26,217,861.75     

          27     27,000,000.00     26,217,861.75          

          28     21,643,000.00     14,926,008.04          

          29     0     490.9          490.9

          30     24,450,000.00     764,160.59

          31     -12     0          

          32     -1,739.00     -51,391.28     

          32     1,739.00     51,391.28     

          33     -100     -2,247.94     

          33     100     2,247.94     

          34     -300     -3,436.08          

          34     300     3,436.08          

          35     -26,449.00     0          

          36     401     1,162.90          

          37     -25     -2,670.50          

          37     25     2,670.50          

          38     -400     -18,220.00          

          38     400     18,220.00     

          39     4,700.00     47,485.04          

          40     -1,200.00     -2,359.20          

          40     1,200.00     2,359.20          

          41     -20     -513.6          

          41     20     513.6          

          42     -80     -5,618.40          

          42     80     5,618.40          

          43     0     11,304.00

            • Help with Summary Filter to exclude 0s
              Data Kruncher

              Hi adonis,

               

              You can't really do that in a single Monarch model. If you're just printing the summary, you could effectively hide the text for the zero rows by using the color formatting and limits (forcing white on white for zero values), but that's a hack at best.

               

              Instead, what you'll need is to export the summary, and then use that export as the input for another Monarch model which then properly filters the zero values out.

               

              Kruncher

                • Help with Summary Filter to exclude 0s
                  adonis _

                  like always, will try that route, let you know if any issues arise, you are a true hero bud.

                    • Help with Summary Filter to exclude 0s
                      Grant Perkins

                      Filters are always applied to the data in the table before it hits the summary.

                       

                      However you could try something a bit trick in the summary that may work for you.

                       

                      As I understand it the basic requirement is that any line with a net zero value in BOTH columns is not required for reporting.

                       

                      So, in your summary add another measure and change it to a formula that sums (or whatever) Net Qty and Net Amount. (You can hide this summary column when you are happy that this works for you.)

                       

                      Now you should see a new column in the summary that will have values only for the records you wish to report.

                       

                      Go into the properties for your Sec # column (or whichever is the appropriate key field level to use) ans open the MATCHING tab. Activate the MEASURE LIMIT radio button and then select the new Calculated Measure field from the fields available to you in the drop down list, set the match to be NOT EQUAL ( <>) and the value to 0.

                       

                      By default you will get an unmatched value row for a count of the zero value records. In V10 you can choose to hide that row is you wish.

                       

                      Set any criteria you wish (i.e. Sort orders, colouring, etc.) in the other tabs and that should give you what you need as I understand the requirement.

                       

                       

                      HTH.

                       

                       

                      Grant

                       

                      Edit: Depending on circumstances I suppose it is possible that two values in columns that both need to be reported could still net to 0? If so this concept may not work for you.