4 Replies Latest reply on Jun 6, 2017 9:37 AM by josealfredogsc

    Sum by date criteria



      Here's my story:

      I have a wholesale shoes store, my clients come and pick up about 50 shoes (flat price shoes), after a month she come back for returning what she did not sell and pay for what she sold.

      I have many multiples payment so I created a payment table and also a returning table.

      I definitly have no SQL code skill so everything I created were using calc and relationship.


      I'm using invoice template and now I need to add a cell (in the dashboard layout) for what I received per month and don't know how to.


      Please some very easy way to do that?

      I saw some solution at our forum here but I tried to implement but unsucessed.

      My file is attached for any doublt

      Thanks a lot.

        • 1. Re: Sum by date criteria

          Do you want to see just the total for the current month or do you wan to see a list of totals, one for each month over the past X months?


          Both are possible.


          A useful calculation to use for sorting to group by month and to use for matching records by month in a relationship:


          DateField - Day ( DateField ) + 1


          This computes the date for the first day of the month of the date entered into DateField.

          • 2. Re: Sum by date criteria

            Hi Philmodjunk,

            An fixed table showing me all the sum sorted by month would be great like:

            Month          Total received

            Jan                    xxxxx

            Feb                    xxxx         

            Mar                    xxxx


            I didn't figure out how the formula you gave works and where I am supose to write it.

            Sorry for my very basic level.

            • 3. Re: Sum by date criteria

              I am assuming that you want that in a portal on your dashboard layout. To do that without SQL you will need a table of months.


              To start, you will only need one field in months, though you can add a primary key field as well if you want to. Make sure that you have a field named Month defined as type date. You may have intended AUX_Month for this purpose, but note that I am describing a different design for that table.


              You'll need one record per month entered into this table. Enter the date for the first of each month. This can be done by hand, or you can create a script to create these records.


              Define this calculation field in Payment, cMonth and select "Date" as its result type:

              Date - Day ( Date ) + 1


              Now use the button with two plus signs to make a new occurrence of Payments, Payments|byMonth. link it to your new Months table like this:


              Payments|ByMonth::cMonth = Months::Month


              Now add a calculation field, cMonthTotal, to Months defined as:


              Sum ( Payments|ByMonth::Amount Paid )


              with a number result type.


              Link Months to company Dashboard in a Cartesian Join just as you linked Invoices to it in a Cartesian join.


              You can now put a portal to Months on your dashboard with the Month and cMonthlyTotal fields in the portal row to show the total payments received for each month. You can use date formatting to format Month to display only the Month and year as the day will not be significant. (By using a field of type date, you have a value that will sort correctly any time you need to sort this data by month.)

              1 of 1 people found this helpful
              • 4. Re: Sum by date criteria

                Hi Philmodjunk,

                No words to say thank to you for soooo much deep details you gave.


                Even with definitly no FM skills I could follow you instructions and finally works!

                Thanks a thousand times!

                God bless you.