13 Replies Latest reply: May 15, 2014 10:07 AM by Olly Bond RSS

    Stacks in the Summary question 10.5

    Rick Conklin

      [FONT="Courier New"][/FONT][FONT="Courier New"][SIZE="3"][/SIZE]Hi -

       

      I'd like to stack data in a different way. Currently, I have an accounting period (e.g. 2010/06, 2010/05, etc) Across the top.

       

      Down the side I have a "category".

       

      I also have three sets of figures: Charges, Payments, quanity billed.

       

      Currently, the summary spreads the three types of figures horizontally for each accounting period. See below.

       

      Currenty summary  (using "all by key")

       

      . . . .  . . .2010/06 .. . . . .2010/05  . . . . . etc

      . . . . . Chrgs  Pmts  Qty   Chrgs  Pmts  Qty

      Category 1     $$      $$     NN    $$      $$     NN

      Category 2     $$      $$     NN    $$      $$     NN

         etc.

       

      • * * * * * *   The question   * * * * * *

      Is there a way to stack the figures vertically- such as below?

       

      . . . . . . . . . 2010/06  2010/05  etc.

      Category 1

      . .Chrgs . . . . $$         $$

      . .Pmts  . . . . $$         $$

      . .Qty . . . . .  NN        NN

       

      Category 2

      . .Chrgs . . . . $$         $$

      . .Pmts  . . . . $$         $$

      . .Qty . . . . .  NN        NN

       

       

      etc.

       

      Is this possible?

       

      Thanks

       

      Rick Conklin

      Wichita, KS

       

      ugh.  the formating when you type the message doesn't match the "preview". It wants to remove extra spaces. Screws up the format I tried to display above.  sorry

       

      email rconklin@MSNLLC.com[/email][/SIZE][/FONT]

        • Stacks in the Summary question 10.5
          Olly Bond

          Hello Rick,

           

          You've got multiple measures, and an across key, so Monarch v10 will let you show them as sub-columns under each across key value. To show the data in the way you want, you'll need to summarise the data one way, export it as a fixed width text file, and then open this in a second model.

           

          I'll mock up an example and post a couple of portable report files on-line to illustrate this approach.

           

          Edit - examples online at http://www.greenbar.info/examples/keys/firstpass.prf[/url] and http://www.greenbar.info/examples/keys/secondpass.prf[/url] - as PRF files including the summaries.

           

          Best wishes,

           

          Olly

            • Stacks in the Summary question 10.5
              Data Kruncher

              Very nicely done Olly. :cool:

               

              Terrific example of the specified values sequence in a practical application too.

                • Stacks in the Summary question 10.5
                  Rick Conklin

                  Olly,

                   

                  That was great. I had never used multi-column before. Got that.  Thank You!

                   

                  My "pass2" summary very nicely has the category, then measure 1,2,3 in the next column (I've rename them).  My accounting periods go across very nice.

                   

                  Now "summary - total" just gives one total ... which is meaningless.

                   

                  My only challenge I have left is this >  I'd like to get overall vertical summaries in each accounting period for each of the three measures.  Is that possible?

                   

                  . . . . Just a cosmetic question. One of my three measure is a "quantity" the others are dollars. With your approach, is it possible to customize the numeric format of one measure differently than the other two?

                   

                  thanks very much,

                   

                  Rick Conklin

                  Wichita, KS

                  V10.5 pro

                    • Stacks in the Summary question 10.5
                      Data Kruncher

                      If you have only one measure field, as is the case in Olly's second example, then no, you can't format the values in both dollars and, say, thousands. One format to rule them all.

                       

                      If you want overall totals for the Charges, Payments and Quantity by period, your best approach is to create another summary (duplicate the first to get started) and then make the field that has the label values of Charges, Payments and Quantity as the first (only? up to you) key field. If necessary add a subtotal for the field. Then use each summary as necessary for the detail level required at the time. You can build a number of summaries in any model, each with a dedicated purpose.

                       

                      Is that what you need?

                      • Stacks in the Summary question 10.5
                        Rick Conklin

                        One more note -

                        My scenario is slightly simpler. 

                         

                        I've only got one vertical key. (column 1) Beside those "measures" (column 2).

                         

                        Using your approach, Olly, I'm almost to the finish line.  But, my user really needs the bottom totals for each measure for all the vertical keys summed up.

                         

                        Any ideas on how to get across the finish line?

                         

                        thanks again.

                         

                        Rick Conklin

                          • Stacks in the Summary question 10.5
                            Rick Conklin

                            Data Kruncher,

                             

                            Sounds like you might be suggesting I filter by the first field to simplify.

                             

                            Reality - I have another field (location).  I am filtering by location to create a unique summary by location.  The user much wants the category as field one, then the measure type vertically under each accounting period.

                             

                            Have I hit the wall here?

                             

                            thanks

                             

                            Rick

                            • Stacks in the Summary question 10.5
                              Data Kruncher

                              What you'll want to do is this:

                               

                               

                              Create two summaries, as described above.

                              Now create a project export to send the first summary to an Excel file, overwrite the existing file if it exists.

                              Create a second project export to append the second summary to the new Excel file, appending data to the existing file and the existing table.

                              /LIST

                              This will get the Payments, Charges and Quantities total appear to be on the bottom of a single summary.

                               

                              It's important that the field names are consistent between both summaries, which they will be if you leave the defaults as is. This ensures that the data from the second export is put in the proper columns in the Excel file.

                                • Stacks in the Summary question 10.5
                                  Data Kruncher

                                  Have I hit the wall here?[/QUOTE]

                                   

                                  Don't give up yet!! We're just getting started! Collectively, we'll work this out with[/I] you.

                                    • Stacks in the Summary question 10.5
                                      Rick Conklin

                                      Tried the two summaries idea.  It can work if we don't "append" the data.  I hoped it would. 

                                       

                                      But, if I create a summary with no category, I DO get the sum total for each measurement under each accounting period.

                                       

                                      But, it won't append to the first summary spreadsheet.  It is not happy when appending if the exact columns are not there.  ie The summary export from the first summary had the category column, but the second summary export did not.

                                       

                                      I was really hoping that would work as the spreadsheet would have the summary rows at the bottom.  Worse case, I have a second spreadsheet with just the "measurement" totals (qty, charges, payments).

                                       

                                      thanks

                                       

                                      Rick Conklin

                                  • Stacks in the Summary question 10.5
                                    Gareth Horton

                                    Rick,

                                     

                                    I think you just need to enable the Total Line for the summary.

                                     

                                    Go to Edit|Summaries, select the summary and click Edit.

                                     

                                    Go to the General Tab, check "Insert total line", set the behavior you want and click OK.

                                     

                                    Thanks

                                     

                                    Gareth

                                     

                                    One more note -

                                    My scenario is slightly simpler. 

                                     

                                    I've only got one vertical key. (column 1) Beside those "measures" (column 2).

                                     

                                    Using your approach, Olly, I'm almost to the finish line.  But, my user really needs the bottom totals for each measure for all the vertical keys summed up.

                                     

                                    Any ideas on how to get across the finish line?

                                     

                                    thanks again.

                                     

                                    Rick Conklin[/QUOTE]

                                      • Stacks in the Summary question 10.5
                                        Rick Conklin

                                        Rick,

                                         

                                        I think you just need to enable the Total Line for the summary.

                                         

                                        Go to Edit|Summaries, select the summary and click Edit.

                                         

                                        Go to the General Tab, check "Insert total line", set the behavior you want and click OK.

                                         

                                        Thanks

                                         

                                        Gareth[/QUOTE]

                                         

                                        Gareth -

                                         

                                        I clicked "summary" but it only gives one total at the bottom instead of three totals (one for Quantity, one for charges, one for payments).

                                         

                                        thanks for the input.

                                         

                                        Rick

                                          • Stacks in the Summary question 10.5
                                            Olly Bond

                                            Hello Rick,

                                             

                                            You may be able to glue the bits together by exporting to named ranges and having macro in Excel to make the worksheet you want. That's beyond my Excel comfort zone, I'm afraid, but I think there are some wise folk here who could help you with that approach.

                                             

                                            Do you always have the same number of columns (months?) across the top? Same number of rows? How automated does it have to be?

                                             

                                            Best wishes,

                                             

                                            Olly

                                      • Stacks in the Summary question 10.5
                                        Olly Bond

                                        Hello Rick,

                                         

                                        I've PM'd you with some other ideas that might be helpful. But yes, Kruncher's right, we can do this in Monarch. I'm thinking of using the power of v10 to export text summaries as separate files based on a key value change, with another showing totals only and then combining the text files together with a batch command before a final Monarch model to prettify the data for Excel.

                                         

                                        Best wishes,

                                         

                                        Olly