8 Replies Latest reply: May 15, 2014 9:53 AM by joey RSS

    Subtotals in Summaries

    Terry Murphy

      Hi, hope you can help...

       

      I have created a summary which is based on a report and also links to three other databases.  Because the data in the report can have many records for the same customer my subtotal works fine, but the linked records will not sum correctly, it just adds the customer total for as many times as there are records in the base report.

       

      To get around this I am having to display these 'looked up' columns as "Unique" which prevents me using totals. Is there any way at all to total unique values?

       

      Any help would be appreciated

       

      Terry

        • Subtotals in Summaries
          Grant Perkins

          Terry,

           

          Are these values all the same? and you just want one of them or did I interpet wrongly?

           

          You could try MIN or MAX rather than SUM or UNIQUE if they are all the same. Or maybe Average but I'm dubious about that one based on your problem description.

           

          I suspect my quick response has missed something I should have thought of ...

           

          HTH.

           

          Grant

          • Subtotals in Summaries
            Terry Murphy

            hi Grant.

             

            the line values are all different in the base report (list of invoices).  The lookups are to the same reports for the previous months, therefore the lookup imports last months TOTAL against each line of data in the base report.  So if this months total was £10,000 then £10,000 appears against each of this months invoices and that's the reason why I can't use Max, Min or Sum.

             

            Does that help at all?  I appreciate your interest.

             

            Terry

            • Subtotals in Summaries
              joey

              I believe that average would come out to the amount, if every unique occurance of the same key has the same imported total.

              • Subtotals in Summaries
                Grant Perkins

                Originally posted by Joey:

                I believe that average would come out to the amount, if every unique occurance of the same key has the same imported total. /b[/quote]Agreed since the single lookup of a fixed value is, presumably, being multipled by the count unless I have completely misunderstood what you have described Terry.

                 

                Likewise, though strangely perhaps, the MIN or MAX values applied to the measure instead of the SUM should also work since both options would give the same result in the lookup values.

                 

                (I am assuming that when you mention looking up the TOTAL from LAST MONTH and then move on to

                 

                " So if this months total was £10,000 then £10,000 appears against each of this months invoices and ..."

                 

                   the 'this' in the first line above referes to the TOTAL previously mentioned as 'LAST MONTH'. If I am wrong there then my apologies and I have obviously missed something in my understanding of your problem.

                 

                 

                Grant

                • Subtotals in Summaries
                  Terry Murphy

                  Sorry guys, I may be confusing you...

                   

                  The repeating balances are in the table whereby each record in the current month brings back the customer total from the previous months.  When we change to the summary we get the following.  Does this make it any clearer? (not that easy to explain in text).

                  [font="courier"]Division     Area       Acc No      Customer     January     March     April     Current

                  SA02         SA0208     A123456     Customer 1        33099.75     23037.16     27279.48     19381.58

                  SA02         SA0208     A234567     Customer 2        5514.9     4308.93     6590.59     8910.03

                  SA02         SA0208     A234568     Customer 3        790.91     455.79     12.19     363.2

                  SA02         SA0208     A456789     Customer 4        17399.46     13521.69     15256.79     14898.13

                  Subtotal                                    1658996.79

                                      Lookup     Lookup     Lookup     Base report[/font][/quote]If the total for an a/c last month was say 154.75 then this figure repeats for each line in the current month therefore the summary adds 154.75 repeatedly if using sum.  if using Unique it returns the correct total for each a/c but that's where i fall down because i can't add them up without exporting to Excel and finding every a/c change, on a 10mb file this will be quite a chore.

                   

                  Terry

                  • Subtotals in Summaries
                    Grant Perkins

                    Terry,

                     

                    OK, so you need to total the current month AND the previous months individually?

                     

                    One option might be to have a record act for each customer as a 'dummy master record to do the lookup for the previous months and do NO LOOKUPS for records with current month values.

                     

                    Not sure if that would be easy or difficult to do in a single pass. Not too difficult in a 2 stage process where one stage just picks the lookup values for each customer and write a record and the second stage picks the current month detail and writes a record to the same file (same format of fields) and the two are then sorted in the table prior to creating the summary output.

                     

                    There may be a single pass answer but I don't have the time right now to set up an analysis. May get back to it later unless Joey beats me to it!

                     

                     

                    HTH.

                     

                    Grant

                    • Subtotals in Summaries
                      Terry Murphy

                      Grant/Joey;

                       

                      I have cracked it.  I found that by exporting the summary into excel without any totals at all and then re-loading it into Monarch I can create all split summaries and totals to my hearts content.  the first pass summary only take just under a minute to run so the 2 stage report is fine.

                       

                      Thank you both for your input.

                       

                      Terry

                      • Subtotals in Summaries
                        joey

                        Originally posted by Grant Perkins:

                        There may be a single pass answer but I don't have the time right now to set up an analysis. May get back to it later unless Joey beats me to it!

                        /b[/quote]A challenge! If I'm understanding the problem correctly, its similar to the one I posted

                         

                        [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001565#000000"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001565#000000[/url]

                         

                        In each case we need to do an aggregation function (sum or count) on the unique set of values in the table.  I couldn't come up with an easier/cleaner way than a two pass process.

                         

                        Glad to hear it is working for you Terry.