6 Replies Latest reply: May 15, 2014 9:54 AM by Data Kruncher RSS

    Formula's

    Pam _

      I am trying to creat a table that will compare

      invoice amounts to certain vendors listed by month.

       

      Here's an example of the current data in the table:

                                        

      Month        Name    Amount                    

      September     Joe      1120.84

      October             Joe      1418.28

      November     Mary      122.83

      December     Bob     3818.80

      November     Joe      4955.00

       

      I am adding columns to the right to represent each month. (I would like to see the vendor name only once and Have a column for each month so I can see the totals side by side).

       

      So it looks like this:

       

         September   October

      Joe   1120.84   1418.28

       

      I need a formula that says if the Month = Sept put the Amount in the Sept Column.  I figured I would need a new calculated field for each month but I can't seem to get the formula to work.

      I entered it as If(Month="September",Amount," ").

      I'm obviously missing brackets or something.

      Can anyone help?

        • Formula's
          Data Kruncher

          Hi Pam and welcome to the forum!

           

          What you want to do is to go to the Summary window and create a Monthly Summary with the Vendor name as the first Key item, and the Month as the next Key item. Once you have the Month field listed, double click on it in the Keys list. Go to the Display tab and set to go Across.

           

          Put your Amount in the Measures box, and I think you'll have what you're after.

           

          Do let us know if you have further questions.

           

          HTH,

          Kruncher

          • Formula's
            Pam _

            Originally posted by Data Kruncher:

            Hi Pam and welcome to the forum!

             

            What you want to do is to go to the Summary window and create a Monthly Summary with the Vendor name as the first Key item, and the Month as the next Key item. Once you have the Month field listed, double click on it in the Keys list. Go to the Display tab and set to go Across.

             

            Put your Amount in the Measures box, and I think you'll have what you're after.

             

            Do let us know if you have further questions.

             

            HTH,

            Kruncher /b[/quote]Thanks!  That worked perfectly.  I have one other issue that came up - Some of the fields are showing up as (Null) and I'd like them to be blank.  I'm not sure where I fix this

            • Formula's
              Data Kruncher

              Go to the Options menu and select View.

               

              Just delete the (Null) text have it appear as blank cells.

               

              Glad to hear that this worked out well for you.

               

              Summaries are terrific tools. When you have a few moments, go exploring and try out some different things. Be sure to double click on your measure fields and see what's available to you there.

               

              Have fun,

              Kruncher

              • Formula's
                Nick Osdale-Popa

                Just to add to Data's post:

                Once you put them to "Display Across", they will be listed in alphabetical order, most likely you don't want this.

                 

                Since we're dealing with Month names this is real simple.

                 

                In the Field Definition in the summary select the Matching Tab[/b]. Click Specified Values[/b] then Edit Values[/b]. Enter in the months in calendar order.

                 

                Once that's done, select the Sorting Tab[/b]. Sort by: Key Field, Position in Specified List[/b].

                 

                Now your columns will be in the correct calendar order.

                 

                Hope that helps!

                • Formula's
                  Pam _

                  Originally posted by Nick Osdale-Popa:

                  Just to add to Data's post:

                  Once you put them to "Display Across", they will be listed in alphabetical order, most likely you don't want this.

                   

                  Since we're dealing with Month names this is real simple.

                   

                  In the Field Definition in the summary select the Matching Tab[/b]. Click Specified Values[/b] then Edit Values[/b]. Enter in the months in calendar order.

                   

                  Once that's done, select the Sorting Tab[/b]. Sort by: Key Field, Position in Specified List[/b].

                   

                  Now your columns will be in the correct calendar order.

                   

                  Hope that helps! [/b][/quote]Thanks so much for all of your help!

                  • Formula's
                    Data Kruncher

                    An excellent bonus tip, Nick. I did neglect that aspect of it.

                     

                    I normally work with Date fields so it just didn't occur to me.