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

    Calculating a "pyramid"


      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"

          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"

            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"

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

              • 4. Re: Calculating a "pyramid"

                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"

                  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"

                    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"

                      > 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"

                        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':



                        • 9. Re: Calculating a "pyramid"

                          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"

                            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"

                              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"

                                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"

                                  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.



                                  • 14. Re: Calculating a "pyramid"

                                    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