17 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Totals per Month

    Renata _

      Hi,I'm trying to create a report that will have totals per month for each stock item.

      I have jan thru april transactions in the Table window and I want to create 4 columns, Jan 2007, Feb 2007, etc. in the Summary.  I've looked all thru the Learning Guide and I just can't seem to find how to do this.  Please help! 

      Thanks,

      Renata

        • Totals per Month
          Nigel Winton

          Renata

          In the summary you need to add your fields, presumably Part Number and Month as Key Fields with the Quantity as a Measure.

          Then you need to double click on the Month field in the Key Area to access the properties window. There you will see a tab Display where you can set the Orientation, set this to across. When you exit from here and the summary definition you should have all of you part numbers down the side and the month across the top.

          You will encounter one problem where the months will display in alphabetical order. You can overcome this by using the month number. You will need to set up a calculated lookup field in the table to convert the month name to number.

          This should then give you the result you are after.

          Let me know if you need any further help.

           

          Regards

           

          Nigel

          • Totals per Month
            Renata _

            Thanks for your prompt reply Nigel.

            I have one column in my summary now titled "Transaction Date".  This column has dates ranging from 1/1/2007 to 4/30/2007.

            I need to divide this data into months so I need to add 4 calculated fields, I assume.  I just can't fiqure out how to get the formula to work. (If I'm on the right track that is...)

            I entered (>=1/1/2007 .AND. <=1/31/2007) * Qty

            but I get operand error messages.

             

            The finished report should look like kind of like this:

             

             

            ____________________   Jan07  | Feb07  | Mar07 

            StockNum1 | Description|  Qty  |  Qty  | Qty

            StockNum2 | Description|  Qty  |  Qty  | Qty

             

            Thanks so much for your help and patience!

            Renata

            • Totals per Month
              Grant Perkins

              Renata,

               

              In the Summary you need to work with a single field (not 4) that uses the value of the month (extracted in a suitable format from the date field and ignoring the day) and display the field 'Across' sorted into the order you require.

               

              Now, this can be slightly easier if you can use the Month as its numeric value in the calculated field as it saves a few steps but no big deal in the long run. To star with let's stick to numerics - we can change it later if you need to.

               

              I will refer to your date source field as "DateField".

               

              In the Table window add a calculated field for MonthYear (name it whatever you want to).

               

              Lets make it a character field (could be numeric if you want to play with getting the value correct for sorting) and use the formula:

               

              [font="courier"]Month(DateField)"/"Year(DateField)[/font][/quote]So that should give the results 1/2007, 2/2007, 3/2007 and so on.

               

              Use the calculated field in your summary as an ACROSS display sorted by value and you will be getting close.

               

              If you need to control the display sort order (especially if you want to use the abbreviated month names) you can specify that for the summary key field.

               

              In the summary right click on the key field MonthYear, go into properties and the "Matching" tab and set up the list of Specified Values. There can only be 12 presumably.

               

              Make sure you enter them in the SORT ORDER you wish to see later.

               

              Leave that Tab and go to the "Sorting" Tab in properties. Check the "Order By: Position in Specified Values list" radio button.

               

              Set the field to display ACROSS using the "Display" tab.

               

              Unless I have missed something out that should get you close.

               

              If you want to see Jan 2007, Feb 2007 and so on you will need to adapt the formula above to interpret Month 1 as Jan, month value 2 as Feb and so on. It is possible to do this using an IF statement or 12 (Nested or sequential) but probably easier to use an internal lookup table to substitute the month name for the numeric value returned by the Month() function. Then adjust the specified values list to suit the order you need.

               

              I'm not sure how much of that you may be familiar with so let us start with the month number approach and get that working then we can go through the quick conversion to Month name to tidy things up afterwards if you need it.

               

              See if you can make sense of what I have suggested.

               

               

              HTH.

               

               

              Grant

              • Totals per Month
                Data Kruncher
                • Totals per Month
                  Renata _

                  Grant with this formula:

                  Month()"/"Year()

                   

                  I'm getting an "Operand Types" error message

                  I removed the second + just to see what else is going on and I'm getting Invalid Delimiter.

                  I tried a "-", "|" and just a space "  " and none of these worked as delimiters.

                   

                  Thanks,

                  Renata

                  • Totals per Month
                    Renata _

                    Data Kruncher,

                    Thanks, that worked.

                     

                    Grant,

                    Disregard previous posting, I'll continue with your instructions.

                     

                    Renata

                    • Totals per Month
                      Grant Perkins
                      • Totals per Month
                        Renata _

                        One more bump...

                        The Display, Orientation choice of "Across" is grayed out so I can't select it.

                         

                        Renata

                        • Totals per Month
                          Grant Perkins

                          Originally posted by Renata:

                          One more bump...

                          The Display, Orientation choice of "Across" is grayed out so I can't select it.

                           

                          Renata /b[/quote]How many Key fields in your Summary? You must have at least one key field set to Down.

                           

                          I'm trying to recall any other constraints that apply ...

                           

                           

                          Grant

                          • Totals per Month
                            Renata _

                            Grant,

                            I have 9 and they are all set to Down.

                            Thanks,

                            Renata

                            • Totals per Month
                              Grant Perkins

                              If you have the Summary key fields set up as Nigel described in the first reply posting I would expect it all to work. (However I am running this from my memory rather than a working example so I may be overlooking something!)

                              • Totals per Month
                                Data Kruncher

                                Post removed: not required.

                                • Totals per Month
                                  Grant Perkins

                                  Originally posted by Renata:

                                  Grant,

                                  I have 9 and they are all set to Down.

                                  Thanks,

                                  Renata /b[/quote]Renata,

                                   

                                  Are any of the fields set up as ITEM fields rather than KEY fields? Item fields and Across display of Key fields are incompatible.

                                   

                                   

                                  Grant

                                  • Totals per Month
                                    Renata _

                                    Ah Ha! I finally notice this handy message at the bottom:

                                     

                                    "Across option is not available when an item field is defined."

                                     

                                    So, I had 3 fields as "Items".  I removed them and was able to choose "Across".  At first glance the report looks good. 

                                     

                                    One more question and I'll give it a rest...for today anyway:

                                    Since I want the fields I had entered as Items in my report, do I add them as Key fields?  I already have 9 Key fields and didn't know if I should keep that to a minimum??

                                     

                                    Thanks so much for your time and help!

                                    Renata

                                    • Totals per Month
                                      Grant Perkins

                                      Renata,

                                       

                                      If you really need the fields it will certainly make the drill up and down feature something to play with!

                                       

                                      It is unlikely that you will hit any technical constraints (See the section on SPECIFICATIONS in the HELP file) but obviously the more running totals Monarch has to maintain for combinations of keys and measures the greater the likelihood of slower processing. Unless you are dealing with hundreds of thousands of records you may not notice a practical difference in performance.

                                       

                                      Under the circumstances I think you should just add the fields back in as key fields and see it the process speeds and results are still acceptable to you. My guess is they will be.

                                       

                                       

                                      HTH.

                                       

                                      Grant

                                       

                                      [size="1"][ October 09, 2007, 05:35 PM: Message edited by: Grant Perkins ][/size]

                                      • Totals per Month
                                        Renata _

                                        Hey Grant,

                                        Yes, it takes a little longer to build the summary but you know what?

                                        It's Exactly what I wanted!  I'm so happy to have this done and I would still be pulling my hair out if it weren't for you, Nigel and DataCruncher.

                                        Thank you so much to each of you!

                                        Best Regards,

                                        Renata

                                        • Totals per Month
                                          Grant Perkins

                                          Originally posted by Renata:

                                          Hey Grant,

                                          Yes, it takes a little longer to build the summary but you know what?

                                          It's Exactly what I wanted! [/b][/quote]:cool:    smile.gif[/img]