1 2 3 Previous Next 31 Replies Latest reply on Mar 14, 2010 3:13 AM by user14360

    Getting the values between two numbers

    user14360

      Title

      Getting the values between two numbers

      Post

      If I have two numbers (eg 2 and 6), is there a calculation I can use to have a field return a list of the numbers between (and including) those two numbers, separated by a return?

       

      ie I want to see

      2

      3

      4

      5

      6

       

       

      Filemaker Pro 10 Advanced, Mac OSX

       

        • 1. Re: Getting the values between two numbers
          comment_1

          It's possible with a custom function - but why would you need this?

          • 2. Re: Getting the values between two numbers
            user14360

            It's the missing piece in what has turned out to be a tricky relationship problem. 

             

            I have a weekly sales data (one record per week) and also a promotions table.  Each promotion has a date range (date from, date to) and there's a relationship from the sales table to the promotions table which is used to populate a field in the sales table with the promotion name, if on the date of a sales transaction there has been a promotion.

             

            Until now the promotions have been limited to staying within a quarter - the sales data is viewed by a relationship which has a global year and quarter field, and it shows the sales within that period, and also the corresponding promotions.

             

            However, people now want to enter promotions that overlap quarters, and it means I'm having trouble getting them to display correctly, because my previous relationship between the sales and promotion table was based on Year = Year and Quarter = Quarter.

             

            The relationship has become

             

            SALES TABLE PROMOTION TABLE

             

            CustomerID = Customer ID

            WeekOfYear = Week Totals (This is where I want to have the field with a return delimited list of the all the weeks of the year the promotion falls on)

            QuarterNo = QuarterTotals (This returns a list of the quarter the promotion falls in, or both quarters if it overlaps)

            Year >= YearFiscalFrom

            Year <= YearFiscalTo

             

             

            I have input the "WeekTotals" field with the appropriate numbers manually, and it returns the right results, so now I just need to be able to have that happen by calculation.

             

            If you can see a less complex/easier way to do this, that would be great, but otherwise a way to return the numbers would be very helpful.

            • 3. Re: Getting the values between two numbers
              comment_1

              Why not go back to the original data:

               

              Sales::CustomerID = Promotions::CustomerID

              AND

              Sales::TransactionDate ≥ Promotions::StartDate

              AND

              Sales::TransactionDate ≤ Promotions::EndDate

              • 4. Re: Getting the values between two numbers
                user14360

                I'd love to do that, but the Date in the Weekly Sales table is always the start of the week (it actually sums up a whole lot of daily records into that one weekly field) and so that date wouldn't necessarily reflect the promotion accurately.  eg the promotion could start half way through the week and therefore it wouldn't get included in that relationship.

                 

                However, I suppose I could add a WeekEnd date in the sales table and use that to capture any promotion falling within that somehow.   

                • 5. Re: Getting the values between two numbers
                  comment_1

                  If the week starts on Sunday, and there is a promotion that starts on Friday of the same week - should it be related?

                  • 6. Re: Getting the values between two numbers
                    user14360

                    The sales week date starts on a Sunday.  If any day of that week falls within a date of a promotion, even if it is just one day of the promotion, it should be recorded as having that promotion against that week.

                    • 7. Re: Getting the values between two numbers
                      comment_1

                      Well then:

                       

                      ...

                      WeeklySales::WeekStart ≤ Promotions::EndDate
                      AND
                      WeeklySales::WeekEnd ≥ Promotions::StartDate

                       

                      should do fine.

                      • 8. Re: Getting the values between two numbers
                        user14360

                        Oh my.

                         

                        I was half hoping someone would pipe up and show me how I was looking at it all wrong and come up with a simpler solution, but I never thought I would go from making it SO complex to being SO utterly simple and elegant!!!

                         

                        And it's so obvious now that I can look at it in a different way.

                         

                        Thanks very much Comment.

                        • 9. Re: Getting the values between two numbers
                          user14360

                          Hi again.  There's an extra complication that is not fitting into this solution.

                           

                          There is another field in the sales table in which I want to record if there was a promotion that ran at any time in that week in the last quarter.

                           

                          This would be fine to do by having a calculation that works out the last year of any given weekly sales record and then matches that up with the week of quarter and the year, but the problem occurs when a promotion overlaps a quarter or overlaps the end of a year.  In my current solution, there would be a match on the Year  and YearLast field (and Quarter and QuarterLast field), which means that you'd only see the promotion in one quarter, not both.

                           

                          Any suggestions about this?  

                           

                          • 10. Re: Getting the values between two numbers
                            comment_1

                            I am afraid I have no idea what you mean. I think we have been there before: in my view, weeks run independently of years. I couldn't understand how your quarters were determined, but I am quite sure that you'll never get all three to be synchronized.

                            • 11. Re: Getting the values between two numbers
                              user14360

                              Sorry about any repetition - I'll try to explain more clearly now about the overall structure and what I'm trying to achieve.

                               

                              I have a better answer now about the quarters - it's been an evolving process for me and I've learnt a great deal.

                               

                              There is a separate FiscalYear table, which has a record for each quarter, including the FiscalYear, the Quarter, the Date start and the Date end.  The dates are put in based on the fiscal year as determined by the accounting calendar of the company.  Each quarter is 13 weeks long and starts on a Sunday, usually around the 27th/28th of the month, depending on when the Sunday falls.  Q1 happens to start in October.

                               

                              There is a relationship between the weekly sales table and the FiscalYear table, which is 

                               

                              WeeklySales FiscalYear

                              WeekDate >=  QuarterStartDate

                              WeekDate <=  QuarterEndDate

                               

                              The FiscalYear and Quarter fields in the Weekly Sales table is determined by the above relationship.

                               

                              Each customer has 13 weekly sales records for each quarter.

                               

                              The main view is on the Customer table, and there are 13 individual portals into the weekly sales records for that customer (each with just one field - the sales field), with a relationship based on a global year and quarter field which the user inputs.  Each portal looks into a particular row of the sales records so that all 13 records appear.

                               

                              There are lots of other fields on this layout, but this is the gist of it.  

                               

                              On top of these sales records is another 13 lots of individual field portals, which look into the promotion field in the weekly sales table for the current year.  That is now working with the date range relationship you suggested.

                               

                              However, what I'm trying to achieve is to have another row of 13 individual portals into the weekly sales records, which will show the field for PromotionLastYear.  This field needs to be a calculation working out whether there was a promotion in the same week of that quarter, but in the previous fiscal year. (So if the user is looking at 2009 Q3, I want them to be able to also see in which weeks the promotion fell in 2008 Q2.)

                               

                              I am a bit stumped about how to have the last year promotions display, particularly in regard to promotions whose dates fall across quarters, as I wouldn't be able to have a relationship like the following:

                               

                              WeeklySale Promotion

                               

                              YearLast  Year

                              Quarter Quarter

                              WeekofQuarter >=  WeekofQuarterPromoStart

                              WeekofQuarter <= WeekofQuarterPromoEnd

                               

                               

                               

                              Sorry if I'm repeating myself or not being clear.  I very much appreciate the assistance.

                               

                              • 12. Re: Getting the values between two numbers
                                comment_1

                                All right. Before I get into this, two questions and a suggestion:

                                 

                                1. Do you view one customer at a time? Or do you need to view more than one at the same time?

                                 

                                2. Why 13 individual portals? If each customer has 13 records, why not one portal with 13 rows?

                                 

                                The suggestion:

                                This would be a LOT simpler if one could calculate the quarter from any given date. If the scheme you describe is correct, then such calculation would be almost trivial, and you could eliminate the FiscalYear year table and all the relationships to it.

                                 

                                About your scheme:
                                If each quarter is 13 weeks long and starts on a Sunday, then a quarter has 91 days and four quarters together make 364 days. Which means that each calendar year, your fiscal year starts a day or two earlier - if it now starts around the 27th/28th of the month, 10 years from now it will start around the 15th. There is nothing wrong with that, if that's what the company wants - but it needs to established as being the rule, so that one can use this rule for the calculation.

                                 

                                • 13. Re: Getting the values between two numbers
                                  user14360

                                  1. Do you view one customer at a time? Or do you need to view more than one at the same time?

                                   

                                  Mostly we view one customer at a time.  This is on a layout with a whole lot of info about sales and calculations about those sales.

                                  However, we also have some report-like layouts which shows a list of the customers (based on the customer table) and then the 13 portal fields of pertinent information, such as the sales, or the promotions.

                                   

                                  2. Why 13 individual portals? If each customer has 13 records, why not one portal with 13 rows?

                                   

                                  It was important to be able to view the sales records in a horizontal fashion, with the 13 weeks going across the page, rather than down.  As far as I was aware, this required 13 portals, either based on 13 different relationships, or based on 1 relationship and pointing to a specific row in the portal.  I went with the first option for a while, but switched to the 2nd option.

                                   

                                  About your scheme:
                                  If each quarter is 13 weeks long and starts on a Sunday, then a quarter has 91 days and four quarters together make 364 days. Which means that each calendar year, your fiscal year starts a day or two earlier - if it now starts around the 27th/28th of the month, 10 years from now it will start around the 15th. There is nothing wrong with that, if that's what the company wants - but it needs to established as being the rule, so that one can use this rule for the calculation.

                                   

                                  It appears that the start of the fiscal year continues moving back one day, until it goes far enough back that they compensate by having a 14-week 1st quarter, so that at the end of that Fiscal Year, the start date of the Fiscal year is back closer to the start date of the calendar month it is in.  

                                   

                                  • 14. Re: Getting the values between two numbers
                                    comment_1

                                     


                                    Hildabeast wrote:

                                    the start of the fiscal year continues moving back one day, until it goes far enough back that they compensate by having a 14-week 1st quarter


                                     

                                     

                                    Well, if you had someone smart there tell you that they do this every n-th year, starting with year y, then ... 

                                    But I guess that's not going to happen, so let's move on.


                                    To get the dates of the corresponding week of the same quarter year ago, you must:

                                    1. Calculate the ordinal number of the week in this year's quarter. Perhaps this could be auto-entered when the WeeklySales records are created - otherwise you can calculate this from the QuarterStartDate in the FiscalYear table, or from a self-join of the WeeklySales table counting the previous records of the same customer;

                                    2. Get the QuarterStartDate of the corresponding quarter of last year (through a relationship matching the global quarter AND global year -1;

                                    3. Calculate the week's start by adding the appropriate number of weeks to the above, and the week's end from that.

                                    Now you can define another relationship to Promotions using those two dates.


                                    I have no idea what's supposed to happen when you are comparing a regular quarter of 13 weeks with a leap one of 14 weeks.







                                    1 2 3 Previous Next