1 2 Previous Next 15 Replies Latest reply on Feb 14, 2017 11:19 AM by fmpdude

    Calculating a "pyramid"

    JesseVickey

      I'd like to calculate the "value" of a customer as the sum of all "value"s of customers they've referred -- essentially a pyramid (see the attached).

       

      - In trying a simple self-join relationship with a sum calc, the value only goes 1 level deep.

      - A "List" calc gives weird results.

       

      Any suggestions?

        • 1. Re: Calculating a "pyramid"
          user19752

          Why 2nd level have sum as sum of value of children but 1st level have sum of sum ? Value of 2nd level is not used.

          • 2. Re: Calculating a "pyramid"
            fmpdude

            What you're describing is a recursive relationship. You'd either have to model it that way (I don't think a simple self-join will work) in the data or use a CF to recursively go through your data.

             

            This kind of structure isn't that uncommon. Each "node" needs a parent id (or maybe two IDS, based on your diagram) and possibly a "child" id, too. Then you have to walk the tree, so to speak. Basically a tree structure.

             

            I'd model the design in data using a real ERD tool first (not the RG) before writing code. Make sure you can manually walk the tree to get your sums, then write code.

             

            HOPE THIS HELPS.

            • 3. Re: Calculating a "pyramid"
              beverly

              I was thinking 'hierarchical' rather than 'recursive'. Either term can be searched along with 'filemaker'.

              • 4. Re: Calculating a "pyramid"
                fmpdude

                Yep, Hierarchical is the structure, recursion is the programmatic method to visit each node. Like visiting each directory in a file hierarchy.

                • 5. Re: Calculating a "pyramid"
                  siplus

                  The structure you show us is not a binary tree, in which a node at level n depends on 2 and only 2 nodes at level n+1.

                   

                  It might be of help if you would kindly illustrate the original problem you're facing, independently of presumed structure.

                  1 of 1 people found this helpful
                  • 6. Re: Calculating a "pyramid"
                    fmpdude

                    He never said it was a simple binary-tree structure at all and indeed documented the structure he needs. And, he stated the actual problem he's trying to solve.

                     

                    I thought the initial diagram was very good as a starting point if that's indeed the structure he's dealing with, but it's up to the OP to now reply.

                     

                    When I do stuff like this, it's usually using nested data structures of any depth. Binary trees are nice when applicable since support for them in most languages is good. Not sure how to craft this type of problem in FMP using global variables and tables (no arrays or other data structures per se), but I'm assuming it could be done with the right data model.

                    • 7. Re: Calculating a "pyramid"
                      JesseVickey

                      > It might be of help if you would kindly illustrate the original problem you're facing

                       

                      Sure. Many of my customers come through referrals of existing customers. So how much is any one customer "worth" to me -- and what can I spend to obtain new ones?

                       

                      Customer A purchased my product enough that I've profited $100. But A has also referred to three new customers (B1, B2, B3) who have also profited me $100. And those B customers have referred to two new customers each.

                       

                      So my goal is to learn the 'value' customer A has meant to my business over time (including "downstream" income) and possibly reward customer A so this behavior continues.

                       

                      • 8. Re: Calculating a "pyramid"
                        beverly

                        good ole "multi-level marketing". pyramid is correct.

                         

                        It's still much like a genealogy (hierarchical) problem and researching 'filemaker hierarchical reporting' will yield many ideas, such as:

                         

                        or just 'filemaker hierarchy':

                         

                        beverly

                        • 9. Re: Calculating a "pyramid"
                          fmpdude

                          Nice job, Bev!

                           

                          I coded the pyramid in about 60 minutes in my language of choice with intermediate and final values. I could post my object graph and "Node" class structure, but your FMP links are more appropriate. In my case, I used a standard tree-traversal algorithm with referential object pointers in each "Node" class. Typical stuff.

                           

                          My only other comment is that the "CF" you referenced above (fmfunctions) is complex difficult-to-debug code. If it works "black box", that's fine. But since you can't debug CFs (as in "step through" in a debugger) it could be a pain to figure out problems. Plus, being recursive it's even more difficult to debug (again, only if debugging is needed).

                          • 10. Re: Calculating a "pyramid"
                            fmpdude

                            Not sure if you got your example working in FMP yet, but it's quite easy using a programming language that supports real data structures. I didn't rely on any "built-in" capabilities for tree-traversal. Just crafted my own. Took about 30 minutes total for the data ("Node") class and maybe another 30 minutes to write the traversal code. The trick here is that a child can have more than one parent (Record E) and a parent can have more than one child (Record B).

                             

                            =========================================

                            Here's the output of my program - using your example:

                            =========================================

                            Now Visiting Node...root with value: 1             // root's value not considered in final sum

                            Now Visiting Node...Record B with value: 3

                            Now Visiting Node...Record D with value: 2

                            Now Visiting Node...Record E with value: 7

                            Now Visiting Node...Record C with value: 6

                            Now Visiting Node...Record E with value: 7

                            Now Visiting Node...Record F with value: 4

                             

                            Final Answer (SUM) Is: 20

                             

                            I thought your diagram with expected sums and node values, and the overall structure presentation, was EXCELLENT documentation.

                            • 11. Re: Calculating a "pyramid"
                              fmpdude

                              For FIleMaker, I came up with the following structure that would work well for me and how I solved the problem.

                               

                              Here's what the data input screen looks like:

                               

                               

                              Here's the Relationship Graph:

                               

                              HOPE THIS HELPS.

                              • 12. Re: Calculating a "pyramid"
                                schamblee

                                The trick here is that a child can have more than one parent (Record E) and a parent can have more than one child (Record B).

                                 

                                The pyramid structure that the OP is referring to would not have more than one parent record.   Only one person can refer a customer.   Each customer could have multiply children records and each child could be a parent with many  children records.    Just a one to many relationship.     

                                • 13. Re: Calculating a "pyramid"
                                  fmpdude

                                  This isn't a simple one-to-many problem (or a simple binary-tree, either).


                                  Look at his diagram.  Record E has two parents ---> B and C.

                                   

                                  Do you see the 7 included in both B & C from E?

                                   

                                   

                                  ---


                                  And Record B has two children: E & D.

                                   

                                   

                                  Try to solve the problem in FMP using your method and post back.

                                   

                                  Heck, maybe you see a trick I missed, though I solved the problem in an hour.

                                   

                                  Enjoy!

                                  • 14. Re: Calculating a "pyramid"
                                    schamblee

                                    I don't know any businesses that pays multiple referrals on one customer, once they been referred they become a customer and no longer can be a  new customer.  Like DirecTV use to have, I could only be referred once but I could refer as many friends and family  as I wanted, then they could refer their friends and family.  A business would run out of money fast paying repeat referrals on the same customer.  

                                    1 2 Previous Next