6 Replies Latest reply on Jan 21, 2014 4:27 AM by PieroF

    Sum_Calculation based on condition



      Sum_Calculation based on condition


       I have created a calculated field in a Company table that sums the total amounts from a related table (Opportunity) field called FuturePotential.

      Sum(Opportunity::FuturePotential). This returns the total $ value of all FuturePotential $ for a specific Company listed in the Opportunity table. This works fine.

      Now I want to only sum where entries in the Opportunity table have a status that are "Open", all related to a specific Company. I've tried the following:

      Sum(Opportunity::FuturePotential ; FilterValues(List(Opportunity::Status) ; "Open)).

      This has not provided the results I'm looking for. Where am I going wrong? I tried an If statement first but as soon as one of the Opportunities was closed, the sum = zero, even though there was still an Opportunity Open.

        • 1. Re: Sum_Calculation based on condition

          If I had a dollar for everytime this issue pops up in the forum....

          I could take my wife out for a pretty nice meal...Undecided

          FilterValues will only return the value "open" or Null, it won't affect what records in the related table are summed. An If or case function can be used here to control whether or not any records are summed, but cannot be used to make the sum function selectively sum records from the related table.

          Option 1:

          If you use sum, the relationship used, must fliter out the values you do not want summed. Either that or you use a completely different method.

          If you defined a calculation field, constOpen as just the quoted text: "Open", you could define this relationship:

          Company::CompanyID = OpenOpportunity::CompanyID AND
          Company::constOpen = OpenOpportunity::status

          Then Sum ( OpenOpportunity::FuturePotential ) will return the desired total. OpenOpportunity is a new occurrence of your existing Opportunity table.

          Option 2:

          If you define a summary field, sTotalFuturePotential as the total of the FuturePotential field in the opportunity table, you can put a one row portal to Opportunity on your layout and place sTotalFuturePotential in that portal row. You then include this portal filter to get only Open status records included in the total: Opportunity::Status = "Open".

          • 2. Re: Sum_Calculation based on condition

             Option #2 works like a charm!

            I wouldn't have come up with that solution in a 100 years. You never cease to amaze. :)

            More calculations are on the way :(

            • 3. Re: Sum_Calculation based on condition

                   I have a same issue like this topic.

                   And maybe you can help me too Phil, as I know you hv done help a lot of people who posted in this forum.

                   I hd created a table called DATABASE 1 QTY

                   which appear all my data such as RECEIVING, TRANSFER, POS, INVENTORY STOCKS, etc.

                   I hv only one DATABASE 1 QTY::Qty field, that record all quantity in every transactions process.

                   and wanted to control it to appear in other several tables, such as ITEMS and STORES, per ITEMS, per STORES.

                   For info, I hv 1 table Store called "MAIN STORE" and 1 other table Store called "ANAK MAS". that 2 table will contain information of my stores.

                   I also have ITEMS table called ITEMS LIST, which contain every details of my Items. In every items, I put 2 field (ITEMS LIST::Unit on MAIN STORE) and  (ITEMS LIST::Unit on ANAK MAS), bcoz I would like to appear the Qty of the selected Item in each store.


                   Here are some calculation that Im willing to do but tottally doesnt work : (for example in Units on ANAK MAS field)


              Case (DATABASE 1 | QTY::Store Name = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) ) 
              - Case (DATABASE 1 | QTY::From Store = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) )
                   Note :
                   Store name field will define stocks comes in
                   From Store field will define stocks comes out.
                   And my layout in DATABASE 1 QTY will looks like:
              Date --- Transaction Type ---Item Name--- Qty --- From       --- To
                   21/8/13--PURCHASED --- Necklace A --- 5 --- (BLANK) --- MAIN STORE
                   22/8/13--TRANSFER --- Necklace A --- 5 --- MAIN STORE --- ANAK MAS
                   23/8/13--POS             --- Necklace A ---3--- ANAK MAS --- (BLANK)
                   I wanted to have  a calculation in my Necklace A record in ITEMS LIST table, that show how many stock left on what stores.
                   It should be appear :
                   Units on MAIN STORE : 0
                   Units on ANAK MAS : 2
                   Can you please help me with this?
                   Thank you so much before!
              • 4. Re: Sum_Calculation based on condition

                     Ok, I would like to pile on here and see if what I'm trying to do is possible.


                     I have 3 tables

                     Table 1

                     Table 2

                     Table 3

                     Table 1 is related by ID and then by a date to match with table 2

                     Table 3 is related to table 2 by ID

                     Currently there is a field in table 1 that calculates the number of hours from table 3 through table 2. This works great and it's calculating perfectly.

                     However, I have an issue, there are some things being calculated that need to be filtered out. In Table 2 there is a field that is checked off if they shouldn't get credit for the item. If it's not checked, it should be null. I have added a field to Table 1, should null for each record. So the fields from Table 1 and table 2 should match when they are both null. But when I add the relationship criteria so that the relationship between table 1 and 2 now have ID, Date, Credit the Field in Table 1 that calculates no longer has any values. 

                     I have checked the fields the records so that some are equal, some aren't but it doesn't seem to be calculating any of them. Any ideas for things I need to check?


                • 5. Re: Sum_Calculation based on condition

                       Last 2 posters, please start a new post.  How is anyone suppose to follow answers or provide help to 3 different posts?  Too confusing.

                  • 6. Re: Sum_Calculation based on condition

                    @PhilModJunk: A summary field in a portal…: wonderful ! I didn't know this solution to get statistics from a filtered portal ! Thanks so much !