1 2 Previous Next 19 Replies Latest reply on May 31, 2016 9:16 AM by sccardais

    Calculate number of months in related records

    sccardais

      I'm creating a report based on a rolling 12 months of data. I don't want to summarize data if there isn't at least 12 months of data.

       

      My data table contains 4,000 records with fields containing a transaction Date and Value and a calculation field that shows the date 12 months prior to the Date field.

       

      A self join relationship finds all of the records within the previous 12 months of the Date.

       

      I'd like to know how many months of related data I have.

       

      For example, assuming my transaction date is 5/1/2016, the start date for my calculation would be 5/1/2015. Assuming I have transactions in each month between these two dates, the result would be 12. If one month had no transactions, the result would be 11.

       

      I'm at a loss as to how to create this calculation.

        • 1. Re: Calculate number of months in related records
          erolst

          You could try

           

          ExecuteSQL ( "

            SELECT COUNT ( DISTINCT ( Month ( theDate ) ) )

            FROM \"YourTable\"

            WHERE theDate BETWEEN ? AND ?

            " ; "" ; "" ; YourTable::startDate ; YourTable::endDate

          )

           

          You could do the same natively, but not without at least one helper calc field, like

           

          cMonthName = MonthName ( theDate )

           

          Then use

          ValueCount ( FilterValues ( "January¶February¶March/* etc. */" ; List ( SelfJoin::cMonthName ) ) )

          • 2. Re: Calculate number of months in related records
            alquimby

            You could set up a related table with one record for each month number. Each record would also have a year field. In the attached, I filled this table once with data in each of 12 records, then created 2 button activated scripts (Add Year and Subtract Year) to change the year values. In your transaction table create Month number and Year calculation fields for your transaction dates and set the relationship. This way you always have a "rolling" view of any 12-month range.

            • 3. Re: Calculate number of months in related records
              sccardais

              I apologize for late reply but I appreciate answers from erolst and Allen.

               

              I'm still having challenges that will take a bit of explanation but I wanted to say thanks while I had a minute.

              • 4. Re: Calculate number of months in related records
                sccardais

                See the attached file based initially on Allen and including erolst calc to count unique months. For some reason, the calc is not calculating the number of unique months in the relationship correctly and I can't figure out why.

                 

                What am I doing wrong here? (For example, see record 70 on layout "Testing Temp")

                 

                My ultimate goal is to summarize (chart) values for 12 calendar periods with each period consisting of 12 months data and in ascending date order.

                 

                e.g. the 12th period would be the current month. It would include the current month and the previous 11 months for a total of 12 months of data.  The 11th period would be the previous month and 11 months of previous data, etc.

                • 5. Re: Calculate number of months in related records
                  erolst

                  sccardais wrote:

                  What am I doing wrong here?

                   

                  The field is calculating OK; but the field instance you're displaying on the layout comes from the self_join TO, not from the layout TO. (Two leading colons in the field name indicate a related field, btw.)

                   

                  Screen Shot 2016-05-24 at 14.26.00.png

                   

                   

                  Change this to use the same field, but from the "Current table (“TRANSDATA”)" TO.

                  • 6. Re: Calculate number of months in related records
                    sccardais

                    Thanks, Erolst.

                     

                    Great. That worked.

                     

                    This is a recurring question for me. I must be looking at the relationships or context incorrectly. In this case, I was thinking that I wanted to count the number of unique months in the join table so the field needed to come from the join table.

                     

                    I’m thinking of the join as a type of filter that creates a subset of the base table so the field that is counting the values needed to count values from the join. Obviously, this is wrong but I’ll be darned if I understand why. One of these days, it will all make sense.

                     

                    Thanks for your help.

                    • 7. Re: Calculate number of months in related records
                      alquimby

                      I don't know if the attached will help you because I'm not sure I understand what you are trying to accomplish. But the attached has the 1031 transaction date records from the file you attached earlier (in the Transaction table). The related MonthCount table shows you the transaction date count for each month between the start and end dates based on any given transaction date entered in the Trans Date field.

                      • 8. Re: Calculate number of months in related records
                        sccardais

                        Thanks, Allen.

                         

                        Here’s a more complete explanation of what I’m trying to do.

                         

                        We collect customer satisfaction surveys. Customers give us a score of 1 - 10. We follow the Net Promoter Score system. Scores of 9 or 10 are grouped as “Promoters.” Scores of 6 or below are considered “Detractors.” The Net Promoter Score is the % of Promoters minus the % of Detractors. This calculation isn’t a problem. I ‘m just explaining the system.

                         

                        Some months we get 50 surveys, some months we get 200 so monthly comparisons can be misleading. To offset this difference, I want to show the trend over time using a column chart with 12 columns. Each column is a 12 month period. So, assuming the right most column is for the 12 month period ending May 2016, it would show the Net Promoter score for all surveys received since June, 2015.

                         

                        The column immediately to the left would be for the 12 month period ending April 2016. It would show the Net Promoter Score of all surveys received since May 2015 … and so on. The leftmost column (1 out of 12) would be for the 12 month period ending June 2015 and would show the Net Promoter Score of all surveys received since July, 2014.

                         

                        This type of chart eliminates seasonal fluctuations and situations like ours where the number of surveys received each month varies. Here an extreme example. Assume we get one survey with a score of 10 in a specific month. It would be very misleading to post this value on a chart all by itself.

                         

                        I hope this helps clarify my end goal. Based on your example file, I think I should eliminate the self-join table and create my reports from a new table (Periods or Month/YR).

                         

                        Thanks again for taking the time to help.

                        • 9. Re: Calculate number of months in related records
                          sccardais

                          Allen:

                           

                          Thanks for your help. The attached is a modified version of the last file you sent. Thank you.

                           

                          This one includes the type of chart I described in my last email. It is based on random data. In the real world, the chart would show actual trends over time.

                           

                          When viewing the chart, remember that each column represents a 12 month period. The x axis shows the ending date of the period so it includes data from the previous 12 months.

                           

                          Thanks again for your help.

                          • 10. Re: Calculate number of months in related records
                            alquimby

                            Your chart has 13 months; it starts one month too early. I think this is more complicated (and has too many fields) than it needs to be. Search this site for Virtual Lists. This seems a perfect candidate for a utility table and a script using ExecuteSQL.

                             

                            Al Quimby

                            • 11. Re: Calculate number of months in related records
                              erolst

                              sccardais wrote:

                              When viewing the chart, remember that each column represents a 12 month period.

                              That's what I understood from one of your earlier posts.

                               

                              But according to your legend and this:

                               

                              sccardais wrote:

                              The x axis shows the ending date of the period so it includes data from the previous 12 months.

                              … each column represents a single month, not 12.

                               

                              What is it?

                              • 12. Re: Calculate number of months in related records
                                sccardais

                                You're right. It does show 13 periods. I'll make that change later. Just wanted to show the concept.

                                 

                                Sent from my mobile phone, so please excuse the brevity.

                                 

                                Scott

                                704-661-5393

                                • 13. Re: Calculate number of months in related records
                                  sccardais

                                  Erolst

                                   

                                  I did a quick test before uploading and I'm pretty sure each column includes 12 months of data. Can't test now. The x axis label should be something like "12 Month Period Ending"

                                   

                                  Thanks for follow up.

                                   

                                  Sent from my mobile phone, so please excuse the brevity.

                                   

                                  Scott

                                  704-661-5393

                                  • 14. Re: Calculate number of months in related records
                                    alquimby

                                    Scott,

                                     

                                         ExecuteSQL is beyond my ability for this challenge, so I chose the brute force method in the attached. The data table has all 1031 records from the files you sent. The Period Start and End fields are figured from the Current Date, so there are 12 periods (months) going back in time. Each data record is placed in a date range that relates to a Period Start and End.

                                     

                                    Al

                                    1 2 Previous Next