1 2 Previous Next 27 Replies Latest reply on Jan 29, 2013 11:01 AM by philmodjunk

    How can I calculate difference between sales values for different quarters, per client?

    SwissMac

      Title

      How can I calculate difference between sales values for different quarters, per client?

      Post

           Every quarter I have to enter the total sales for that quarter for each client. I would like to compare this to the previous quarter on a per client basis and have searched on the forum but can't find anything. How can I do this please? I am calculating the quarter name eg "2012 Q2" from the relevant date field at the end of each quarter but don't know how to make FMP calculate:

           Sales 2012 Q2 - Sales 2012 Q1

           or

           Sales 2012 Q1 - Sales 2011 Q4

           How do I identify which exact record FMP should take? It would be great if FMP always took the most recent quarter and compared it to the one immediately preceding it, or with zero if nothing is there.

           TIA

        • 1. Re: How can I calculate difference between sales values for different quarters, per client?
          philmodjunk

               And how have you structured the data from which you would calculate this total?

               You can either use a relationship that matches by dates to get quarterly totals, you can do a summary report where you sort your records by quarre to get a quarterly total, or, if you have FileMaker 12, you can probably find a way to use ExecuteSQL to get quarterly totals.

               But it's a bit difficult to say more than that without knowing more about the data you have and how it is organized within your database.

          • 2. Re: How can I calculate difference between sales values for different quarters, per client?
            SwissMac

                 Well, I haven't created the table yet but I imagine it will be:

                 Client --< Values

                 Where Values contains

                 ValueID
                 ClientID
                 Date
                 Quarter
                 QuarterValue

                 What I can't work out is how to compare QuarterValue for the same ClientID for different quarters where a quarter is written using the format "2012 Q1, 2012 Q2... etc"

                 NB I am not trying to sum values within a quarter; each quarter only has one value as they are only calculated once each quarter in real life. There is only one record for each quarter. I want to measure the percentage change between each quarter, per client. Eg:

                 2012 Q1 = 100
                 2012 Q2 = 110
                 Change = 10%

                 for Clients 1..n

                 I just can't visualise how to use the name of the Quarter in which the valuations are for to identify different QuarterValue values in a calculation. Obviously the names of the quarters used in the calculation will never be the same as time goes by, since I will always be comparing the most recent quarter value with the immediately preceding quarter value, or perhaps the very first quarter in the year. It might be nice to be able to specify an earlier quarter not in the same year from time to time, but this is non-essential. 

                 It looks like it should be simple, but I don't get it. Should I use a Global Field in some way, perhaps for the Client ID in the Values Table? Or is there some clever technique or formula for this I can't find? Maybe a variable? Or is it so simple that I am missing the glaringly obvious?

                 TIA

            • 3. Re: How can I calculate difference between sales values for different quarters, per client?
              philmodjunk

                   You can define a self Join Relationship:

                   Values----<PreviousQuarter

                   Values::ClientID = PreviousQuarter::ClientID AND
                   Values::Quarter > PreviousQuarter::Quarter

                   Then specify a sort order in the relationship for PreviousQuarter that sorts on the Quarter field in descending order.

                   PreviousQuarter would be a new occurrence of Values created in Manage | Database | Relationships by selecting Values and then clicking the duplicate button (two green plus signs).

                   Then values::QuarterValue - PreviousQuarter::QuarterValue will compute the difference between this quarter and the preceding quarter for the same client.

                    

              • 4. Re: How can I calculate difference between sales values for different quarters, per client?
                SwissMac

                     Thanks, that's very helpful (although for some reason I seem not to be able to mark it as such in the Forum).

                     But, if PreviousQuarter is a duplicate of Values, how does FM know that I want to compare the Last and Next to Last records? I don't understand that in the eg you posted?

                     Values::ClientID is currently the Foreign Key to Client::ClientID where Client::ClientID is the Primary Key for the Client table. Does that change anything?

                • 5. Re: How can I calculate difference between sales values for different quarters, per client?
                  philmodjunk

                       PreviousQuarter is a duplicate occurrence of the values table. It's reference to the same table as values under a new name so as to make this relationship possible. This "multipredicate" (matches by more than two pairs of match fields) relationship matches by client ID and by a field that identifies the quarter. This relationship matches to all values records for the same client from previous quarters. The sort order then makes the related record with data from the most recent quarter the first related record--and this will be the record for the same client from the immediately preceding quarter.

                  • 6. Re: How can I calculate difference between sales values for different quarters, per client?
                    SwissMac

                         Thanks for your continuing advice.

                         I must have done something wrong because I don't have a One to Many relationship - it's a Many to Many and I don't know how to change this?

                         Client ----< Values >----< PreviousValues

                         linked by dragging Values::ClientID (FK) onto PreviousValues::ClientID (where it is NOT a FK) AND Values::Quarter onto PreviousValues::Quarter

                         Should this self join be independent of the rest of the relationships in my database, ie a different relationship chart in the Relationship window? And how do I get this relationship to be One to Many?

                    • 7. Re: How can I calculate difference between sales values for different quarters, per client?
                      philmodjunk

                           Client ----< Values >----< PreviousValues

                           looks correct.

                           Did you double click the relationship line between values and previousValues? You need to do that to specify the correct relationship operators.

                      • 8. Re: How can I calculate difference between sales values for different quarters, per client?
                        SwissMac

                             What are the correct operators? I have left all boxes unticked, set the sort as you suggested, but left the relationship operator as '=' but maybe I should have altered this somehow? I have not done this before and the Filemaker help doesn't help much.

                              

                             I have the PreviousQuarter table on the left, and the Quarter table on the right in the relationship box. Which operators should I put faving which way for each of the linked fields?

                        • 9. Re: How can I calculate difference between sales values for different quarters, per client?
                          philmodjunk

                               Note the operators in red in my previous post:

                               Values::ClientID = PreviousQuarter::ClientID AND
                               Values::Quarter > PreviousQuarter::Quarter

                               You'll need to open up that dialog box in order to change = to > for the second pair of match fields in this relationship.

                          • 10. Re: How can I calculate difference between sales values for different quarters, per client?
                            SwissMac

                                 I did try to post a long reply but the forum blocked it and didn't even let me go back to the page where I wrote it! Grrr. It isn't as if this forum has sparkling performance!

                                 I will need to create a new field called Quarter::Change which will be a calculated field (unsaved) working out the percentage change between the two values. Can I just add this to an existing layout based on table Quarter? That's where the current Quarter::Value field is displayed, in a report layout. Or does it need its own layout?

                            • 11. Re: How can I calculate difference between sales values for different quarters, per client?
                              philmodjunk

                                   I sometimes copy long posts to the clipboard before submitting. Of course, then it posts just fine and it's the time I forget to copy that a glitch eats my posts...angry

                                   Does "Quarter" represent your name for the table I named "Values"?

                                   If so, then you can define this calcualtion in the Quarter table.

                                   Values::value - PreviousQuarter::value

                                   would, for example, compute the difference in Value between the value for this quarter and the previous quarter (for the same client).

                              • 12. Re: How can I calculate difference between sales values for different quarters, per client?
                                SwissMac

                                     oops! Sorry. I meant Values, not Quarter. I rushed after taking my time the first time. My first post asked if it was correct I had a sideways hourglass or angular infinity symbol in the relationship box between the two tables Values and PreviousValues?

                                     As for the calculation, I want the answer to be a percentage, not just the value. Does this mean I should create two fields, one for the difference, and one for the percentage change? And when you say in the Value table, presumably you mean a new calculated field (unsaved)?

                                      

                                • 13. Re: How can I calculate difference between sales values for different quarters, per client?
                                  philmodjunk

                                       If you can compute the difference, you can compute the percent change:

                                       ( Values::value - PreviousQuarter::value ) / values::value

                                       Frankly, I forget off hand whether you should divide by the previous value or the current value, but what I have posted should give you the idea on how this is done.

                                       When you have multiple pairs of match fields and they don't all have the same operator as is the case here, you get that symbol to indicate that you have to open the relationships dialog to see what operators are being used.

                                  • 14. Re: How can I calculate difference between sales values for different quarters, per client?
                                    SwissMac

                                         Brilliant! It seems to be generating results I would expect now. To work out the change as a percentage you need to divide the change by the original amount ie PreviousValue::value so you compare the difference to what it was before.

                                         Using this same realtionship is it possible to compare over a longer time period than just since the last quarter, eg since the First Quarter to get a Year to Date figure, or even by a full year?

                                    I'm off for a few days break tomorrow but may have some further questions when I get back. Thanks for your help and Happy Holidays!

                                    1 2 Previous Next