4 Replies Latest reply on Mar 24, 2014 10:08 AM by Dekade

    Case/Sum/GetRepetition CalculationTrouble



      Case/Sum/GetRepetition CalculationTrouble


           I have a workaround in mind but I'd rather have the below attempted calculation work. The workaround will take some time and I just want to get this little area functioning.

           I didn't want this to get lost in the shuffle from yesterday. I am still interested in knowing if there is any way that I can get my calculation to work.

           Notice the layout picture. There are 14 repetitions that represent 2 sets of 7 days (Saturday thru Sunday per each set). I have two calculations. One calculation for each 7day set of repetitions. The calculations are identical except for the repetition field numbers.

           I am wanting a calculation that adds ONLY those repetitions where "Frozen" has been selected. Thus my calculation should return a value of 17.23; but it does not.

           Here is my calculation. Can anyone help me with what I am overlooking and/or doing incorrectly in writing the calculation?

           FreezeYesNo = "Frozen" ;
           Sum (
           GetRepetition (TimestampActualDailyOT ; 1 );
           GetRepetition (TimestampActualDailyOT ; 2 );
           GetRepetition (TimestampActualDailyOT ; 3 );
           GetRepetition (TimestampActualDailyOT ; 4 );
           GetRepetition (TimestampActualDailyOT ; 5 );
           GetRepetition (TimestampActualDailyOT ; 6 );
           GetRepetition (TimestampActualDailyOT ; 7 )))

           Thanks for your help.



        • 1. Re: Case/Sum/GetRepetition CalculationTrouble

               I see no reason to use a repeating field here. You usually get simpler calculations and greater flexibility if you replace your repeating field with a portal to a related table.

               I'm not sure that I understand the intent of your calculation. You have what appears to be a 14 repetition repeating field where either the repetition stores  the text "Frozen" or is empty. Yet your calculation only refers to the first repetition of that field:

               FreezeYesNo = "Frozen"

               And if that first repetition is "Frozen" it sums the first 7 repetitions of the 14 repetition field of timestamps. If it is not, it returns a null (empty) result.

          • 2. Re: Case/Sum/GetRepetition CalculationTrouble

                 Thanks PhilModJunk,

                 I have not included (yet) in the calculation repetition fields 8 thru 14 because I am experimenting right now. Not a real good excuse is it?; I realize it is not.

                 I have a field that is called "FrozenTotalHours". It is to return the total of hours where "Frozen" has been selected from the 14 repeating fields. In this case there are two repetitions where "Frozen" has been selected. I don't even have a second choice other than "Frozen" at this point. If I proceed with this method that I am attempting I will have to add another option which will probably be "No". Anyway, I want the "FrozenTotalHours" field to return the sum total of "17.23".

                 I thought that FM10ADV would go through the calculation that I wrote and grab the fields next to the radio button selections of "Frozen" and add them up. I did not realize that I am only referring to the 1st repetition of the field. And I don't understand how that I am doing that.

                 With what I have just told you do you think there is a way to conquer my madness? I don't really want to go through the pain and time (and stress for me anyway) of learning the portal method unless you insist that it would serve as the only 100% suitable solution to my situation.

                 What do you think?

            • 3. Re: Case/Sum/GetRepetition CalculationTrouble

                        I thought that FM10ADV would go through the calculation that I wrote and grab the fields next to the radio button selections of "Frozen" and add them up.

                   Not with the expression that you have posted as it only tests the value of the first "frozen" repetition. You'd need to test each individual repetition for the "frozen" text. This is another reason why a related table and a portal make for simpler set ups.

                   I very rarely work with repeating fields for the reasons I have already posted. I think that you need two calculations:

                   Define a repeating calculation field, cIfFrozen with 14 repetitions and this expression:

                   If ( FreezeYesNo [ Get(CalculationRepetitionNumber) ] = "Frozen" ; TimestampActualDailyOT [ Get(CalculationRepetitionNumber) ] )

                   Then define a second calculation field as:

                   Sum ( cIfFrozen ) // no need to list each repetition, sum will sum all the repetitions of cIfFrozen

              • 4. Re: Case/Sum/GetRepetition CalculationTrouble

                     Thanks PhilModJunk,

                     I understand your repeating field hesitations. You are respectfully notorious on that stance. However, I greatly appreciate you offering a solution. I will take a few days and see if I can get your suggestion to work. I'm kinda slow at FM.

                     I'll post back should I run into a problem(s).

                     Thanks again.