1 2 Previous Next 15 Replies Latest reply on Mar 2, 2012 1:40 AM by Claw'd

    Calculate total sales in current calander year

    noushie

      Could someone please help me. I want to be able to total all sales made in the current calander year.

        • 1. Re: Calculate total sales in current calander year
          ariley

          The cleanest method would be to add the sales to a field every time you make one, and deduct, if you had to refund the funds.you could have a global number field that you add to.

           

          Now, if this is not an option because you want to tally up something that's already there and not going forward, you could create a calculation. You would need to link to the sales table via the sale date to another table that has the current date and the first date of the year. Then sum the sales amounts from the second table.

           

          Hope thus helps.

           

          Best,

          Agi

           

          Sent from my iPad

          • 2. Re: Calculate total sales in current calander year
            innodat

            Not questioning your answer, just trying to understand...

             

            Assuming that every sale is a separate record and they are all stored in the same table, wouldn't a simple list view with a summary field do the trick? The perform find for date range could be scripted. This would provide the option to look at sales not only for the calendar year, but by month, week, day, random dates - and provide lot's of options for reporting with sub-summaries.

            • 3. Re: Calculate total sales in current calander year
              comment

              noushie wrote:

               

              Could someone please help me. I want to be able to total all sales made in the current calander year.

               

              Either find all sales in the current year and use a summary field to produce the total, or define a relationship to show only records from current year and a calculation field to sum them.

              • 4. Re: Calculate total sales in current calander year
                Claw'd

                Create these fields:

                 

                Year = Year(DateCreated) or InvoiceDate

                 

                 

                ProductIDPlusYear = Product ID & "T" & GetAsNumber(Year)

                 

                Where ProductID is a unique identifier for each unique part sold

                 

                 

                ProductIDPlusCurrentYear = Product ID & "T" & Year(Get( CurrentDate))

                 

                 

                Create relationship based on the two fields above

                called say: RetailSparesSaleList_ProductIDPlusYear

                 

                 

                Change the name to suit your current table name, mine is called RetailSparesSalesList and this is the line item table for the RetailSparesSalesInvoices Table

                 

                 

                If you only have one table with the invoiced sales items in use that.

                 

                 

                TotalSales = InvQuantity * SoldPrice

                 

                 

                YearSalesByPart = Sum(RetailSparesSaleList_ProductIDPlusYear::TotalSales)

                 

                 

                This gives a rolling twelve months that automatically gives sales in the calendar year. the downside is that the more sales the slower the calculation but in the main it works reasonably well.

                 

                 

                Otherwise use the summary based on a date range as mentioned by Michael Huber.

                • 5. Re: Calculate total sales in current calander year
                  ariley

                  He wanted ALL sales not sales per product.

                   

                  Sent from my iPad

                  • 6. Re: Calculate total sales in current calander year
                    Claw'd

                    Yes, what I've proposed does just that assuming that the sales have a unique ID per item which they should if the database is set up properly

                    • 7. Re: Calculate total sales in current calander year
                      Claw'd

                      noushie I think you need to tell us more about the DB structure: where the sales are kept, are they in one table, in one file or scattered about the place:)

                      • 8. Re: Calculate total sales in current calander year
                        noushie

                        Every sale is a separate record and they are all stored in the same table. I want to have a field at the base of a portal summarising total sales for that client in the current year. I already have a Total field summarising total sales made over the life of the clients relationship with the company.

                        • 9. Re: Calculate total sales in current calander year
                          Claw'd

                          How is the portal displaying the records?

                           

                          Is it all one customers records, all customer records, records for a calendar year for one customer and what relationship are you using to display the portal records. this info will help to get the best solution.

                          • 10. Re: Calculate total sales in current calander year
                            Claw'd

                            Sorry, I misunderstood, you are correct another sum would be neede to add up all the totals over the entire year

                            • 11. Re: Calculate total sales in current calander year
                              Claw'd

                              Assuming a stored CustomerID in the Invoice Table.

                               

                              Calculated fields: 'CIDPusYear' = CustomerID & " " & Year(CurrentDate) Unstored, 'CIDPusYearSales' = CustomerID & " " & Year(InvoiceDate) Stored

                               

                              Self Join Relationship between the two fields in the Invoice Table (CustIDToInvoiceIncDate)

                               

                              Calculated Field: sum(CustIDToInvoiceIncDate::InvoiceTotal) In the Invoice Table

                               

                              New layout in Invoice Table with a portal from the new relationship, show whichever fields you want from the Invoice Table

                               

                              Place the Sum(CustIDToInvoiceIncDate::InvoiceTotal) The field will show the rolling year sales for each customers record you are on.


                              To smarten it up you could have the 'CustomerID & " " & Year(CurrentDate)' field reference a global field instead of the Year(CurrentDate) containing a drop down of years then it would be easy to compare by changing the year in the global.


                              Assuming Fields called CustomerID and InvoiceTotal, change to suit.


                              Haven't tried it with lots of records and there are probably better ways so see how it works for you.

                              • 12. Re: Calculate total sales in current calander year
                                comment

                                Since version 7, you don't need to concatenate matchfields. A relationship defined as:

                                 

                                Clients::ClientID = Sales::ClientID

                                AND

                                Clients::cCurrentYearStart < Sales::Date

                                 

                                will work just as fine.

                                 

                                 

                                ---

                                cCurrentYearStart would be an unstored calculation field (result is Date) =

                                 

                                Get (CurrentDate) - DayOfYear ( Get (CurrentDate) )

                                • 14. Re: Calculate total sales in current calander year
                                  noushie

                                  Sorry, I'm not quite sure which fields not to concatenate. Which does the above relationship replace?

                                  1 2 Previous Next