1 2 Previous Next 21 Replies Latest reply on May 7, 2009 2:30 PM by mrvodka

    Dynamic figures by year calculation



      Dynamic figures by year calculation


      Hello All,

      I am trying to do comparisons of sales between a month and the previous year's same month.

      So say this month so far, i have $50,000 in sales, I want to compare that to May 2008 sales.


      I have the dates down. I can show this month and i can show all of the previous year month.

      The problem that I am running into is making the previous year month dynamic as the current month is.


      This is how my calc work now.

      This May 2009 so far i have $50,000 in sales, May 2008 it will show $250,000 in sales.

      It is showing the entire past month, not keeping it up to date with where we currently are in the current month.


      I would like it to show this month's sales up until today and the previous years month up until the current date.

      May 4th 2009 - $50,000

      May 4th 2008 - $48,000


      This way we can see how the month is going without having to wait until the end of the month to compare figures.

      Please help!


        • 1. Re: Dynamic figures by year calculation

          Create a calc field as such with text result:


          Let ( d = Get (CurrentDate);

                  Date ( Month ( d ); 1; Year ( d ) - 1 ) & ¶ &  Date ( Month ( d ); Day ( d ); Year ( d ) - 1 )




          Now create a relationship from this calc field to YourSalesDate field. Use the relationship as such:

          cDateRange  ≥ YourSalesDate

          cDateRange  ≤ YourSalesDate



          This new relationship will now return all records that are in that date range. You can just put a summary field on the parent layout based off this new relationship.

          • 2. Re: Dynamic figures by year calculation

            Very cool.

            That worked perfectly.

            The only problem that i am seeing now is comparing past months.

            It's May now, and if i wanted to compare April 09 to April 08 sales, it only shows up to the 4th of April and not the whole month since it has past.


            Any ideas?

            • 3. Re: Dynamic figures by year calculation

              Here's an expression Comment shared in another thread:


              Let's call your date field: YourTable::TheDate


              Create a calculation that returns date:


              YourTable::TheDate - Day(YourTable::TheDate) + 1


              This calculation preservers the month and year but converts the day value to "1"


              Thus, to find all records found in April, 2009, you would search for 4/1/2009.

              A modification of this calculation would give you the previous year, same month:


              Let (d = YourTable::TheDate - Day(YourTable::TheDate) + 1;

              Date(Month(d); Day(d); Year(d) -1))


              If you want to use today's date, replace YourTable::TheDate with Get(CurrentDate).

              • 4. Re: Dynamic figures by year calculation

                You should probably use a second relationship. You can use the method that Phil stated. On your parent side your unstored calc with Date result will be:


                Get(CurrentDate) - Day( Get(CurrentDate) ) + 1


                and on your Child side of this relationship you will need a STORED calc with date result of:



                YourSalesDate - Day( YourSalesDate ) + 1


                • 5. Re: Dynamic figures by year calculation

                  Thanks for all of the help.

                  But i'm actually using arrows to get from month to month using a target - and + script.

                  So i would want the calculations to be automatic when flipping from month to month.


                  I do not want to have to search for anything. I have several calcuations that are already in use, i just can't figure out this one.

                  • 6. Re: Dynamic figures by year calculation
                       Using relationships with the suggested calculated date fields functioning as the keys should make this possible for you.
                    • 7. Re: Dynamic figures by year calculation

                      I understand.

                      But as I added the field onto my layout and tried to flip through the months, the field only reflects my current sales of May so far on all months. The figure does not change at all.

                      • 8. Re: Dynamic figures by year calculation

                        And what relationship did you use?


                        Define the following two fields:


                        YourTable::ThisMonth = YourTable::TheDate - Day(YourTable::TheDate) + 1

                        YourTable::ThisMonthLY = Date (Month(YourTable::TheDate) ; 1 ; Year(YourTable::TheDate) - 1)


                        Put your table's name in place of YourTable, and your date field's name in place of TheDate.


                        Create a new table occurrence for YourTable, call it SameMnthLastYear.


                        Set up the relationship:  YourTable::ThisMonthLY--=--SameMnthLastYear::ThisMonth


                        Now you can set up calculations and/or a portal that refers to records of the same month but one year earlier.


                        Sum(SameMnthLastYear::SalesAmt), for example will give you the total of "SalesAmt" for all records of the same month, one year ago.


                        Make sense?

                        • 9. Re: Dynamic figures by year calculation

                          I wished you have been forecoming with this information earlier in your orig post. This is different criteria from just asking what the data from the current month of last year is.


                          IF I understand you correctly, you need to store the date into a global date field. You can set it to the current date as part of the opening script.


                          The script that changes it to one month past / forward can just add a month or subtract a month to that global date field (let's call it gDate).


                          So to subtract from the gDate field one month, the script can set the field as: Set Field [ gDate; Date ( Month ( gDate ) - 1; Day ( gDate ); Year ( gDate ) ) ]


                          To Add one month:

                          Set Field [ gDate; Date ( Month ( gDate ) + 1; Day ( gDate ); Year ( gDate ) ) ]



                          Now your calc for the total for the month selected in gDate will be:

                          Date ( Month ( gDate ) ;1; Year ( gDate ) )   // or you can use gDate - Day( gDate ) + 1


                          For the previous year

                          Date ( Month ( gDate ) ;1; Year ( gDate ) -1 )



                          As pointed out earlier, the STORED calc for the child side will still be: YourSalesDate - Day( YourSalesDate ) + 1



                          Now I dont know if you want the gDate selection to also choose your "partial months" of if you always want that to reflect the current month. You didnt really specify but I will list it out anyway. If you want the date that you select to also reflect for the partial then all you have to do is replace the value of 'd' from Get (CurrentDate) to gDate.


                          Let ( d = Get (CurrentDate);  // replace with gDate

                                  Date ( Month ( d ); 1; Year ( d ) - 1 ) & ¶ &  Date ( Month ( d ); Day ( d ); Year ( d ) - 1 )



                          • 10. Re: Dynamic figures by year calculation

                            I apologize for not being clear with what I was doing. I thought that I had explained it.

                            I am alreayd using a global field for my date and I have done all of the calculation just as you explained.

                            The previous year, does work.

                            But for the final sales calculations it still shows the entire month of the previous year, not just up until the current date.

                            This is fine to use on past months. But for the current month, it does not work.

                            It shows all of May 2008 sales, not just up until May 5, 2008 so that I can have a good comparison.


                            I'm doing everything just as you say, so i'm not sure why it is not working.

                            • 11. Re: Dynamic figures by year calculation

                              OK, now I get it. You want a "this month to date" compare to "same date range but last year". You did say that and I missed it.


                              You could use two pairs of key fields in your relationship and use inequality operators to do this.


                              Key1: Date(month(YourTable::GlobalDateField); 1 ; Year(YourTable::GlobalDateField) -1)


                              Key2: Date(month(YourTable::GlobalDateField); Day(YourTable::GlobalDateField) ; Year(YourTable::GlobalDateField) -1)


                              Set your relationship as follows:


                                      YourTable:: DateField-->--YourTable 2::Key1

                              AND YourTable:: DateField--<--Yourtable 2::Key2

                              • 12. Re: Dynamic figures by year calculation

                                You need 4 different relationships. I have a feeling that you are trying to use the same ones. If you have them, then it should be working for you.



                                Here is a sample file. I dont know how to post it here so I am going to post it on your other post in fmforums.com



                                FM Forums Link to your Post

                                • 13. Re: Dynamic figures by year calculation

                                  Yours works perfectly!

                                  And I know that this should work.


                                  I know that i'm going to be in trouble here for not saying this before, but my sales amount and the layout that i'm putting these calculations are in different databases.

                                  I really do not want to put the calculations of key 2 into my sales database because i'll have to make a relationship between them.

                                  I have a relationship in my new "Control Panel" db that is what we are working on now, so that I can pull in the info from the sales db. But the control panel is password protected, so if I made a realtionship in the sales db, i already know what will happen. A popup box will appear on everyone's computer asking them to sign in to the control panel, and no one has the password, Nor do we want them to have it.


                                  Is this something that we can work around?


                                  • 14. Re: Dynamic figures by year calculation

                                    Sorry, I am not sure I fully follow you here. I got that you have two seperate files which is fine. Also I got that not everyone has access to the control file but they DO have access to the sales file.


                                    But I dont know where their entry point is, where the layout exists that they will be looking at data from, etc. 



                                    1 2 Previous Next