7 Replies Latest reply: Jun 15, 2012 4:54 PM by Jose Tos RSS

    results using above

    Jose Tos

      Hi,

       

      I have a question. Here is the thing: I have a straight table with information about work hours per client and month, so clients contract a number of hours each month and we want to measure how much hours we´re really doing and if we are under the required hours at the end of the month, we have to add the 50% of the difference to the contracted hours for the next month.

       

      Difference = (To do)-(Did)

      Add next month= Difference *0.5

      To do= Contracted + Add next month (except the first month of contract that is Contracted)

       

      ClienteMesContractedTo doDidDifferenceAdd next month
      Client XMar868648241
      Client XApr861271711055
      Client XMay861411004121
      Client XJun86107297839

       

       

      I´m using this expression to calculate the column 'To do' and is working fine:

       

      if(not isnull(above([Add next month],1)),Contracted+above([Add next month],1),Contracted)

       

      The problem is that if I select a month, above([Add next month],1) returns null to me and this is the result:

       

       

      ClienteMesContractedTo doDid(To do)-(Did)Add next month
      Cliente XMay8686100-14-7

       

      I´m trying to do the operations in the load but it seems impossible because, as you can see, 'To do' is calculated from 'Add next month' and we need 'To do' to calculate  'Add next month' ....

       

      Any ideas??

       

      Thank you,

      Chema.

        • Re: results using above
          Jose Tos

          Something??

          • Re: results using above
            ioannis giakoumakis

            If both Cliente and Mes are dimensions, you should use above(total [Add next month].    

            above([Add next month] works only with one dimension.

              • Re: results using above
                Jose Tos

                Yes, Cliente and Mes are dimensions.

                I´ve tried your solution but it doesn´t work.

                 

                Thank you for your response

                  • Re: results using above
                    ioannis giakoumakis

                    Sorry, was not thinking...

                     

                    rangesum(Above(total ((Contracted - Did) * 0.5), 0, RowNo(TOTAL)) )

                     

                     

                    see attached file

                    I messed up month a little to order it but you can easily fix that.

                      • Re: results using above
                        Jose Tos

                        Hi,

                         

                        This would be my data:

                        ClienteMesContractedDid
                        Client XMar864
                        Client XAbr8617
                        Client XMay86100
                        Client XJun8629

                         

                        So the other columns are calculated from these two columns.

                        The results of my table are ok:

                         

                        To do
                        86
                        127
                        141
                        107

                         

                        Your column is:

                         

                        Cumulative
                        41
                        75.5
                        68.5
                        97

                         

                        I don´t understand what is it doing with this expression, what's Rowno(TOTAL) ??

                         

                        Thank you, I hope that we are getting closer

                          • Re: results using above
                            ioannis giakoumakis

                            Sorry but you confused me.

                             

                            Contracted     Did     Difference     Difference*0.5     AddNextMonth     Cumulative

                            86                 4        82                41                     41                        41

                            86                 17      69                34.5                   34.5                     75.5 (41 + 34.5)

                            86                 100    -14               -7                       -7                        68.5 (75.5 - 7)

                            86                 29      57                28.5                   28.5                     97 (68.5 + 28.5)

                             

                            unless you do not want half of the remaining to be added to next month or I have not understand at all.

                             

                            or maybe it is not 86 for each month but for all. I don't know you should explain.

                             

                            rowno(total) returns the row number if you have more than 1 dimension.

                              • Re: results using above
                                Jose Tos
                                ClienteMesContractedTo doDidDifferenceAdd next month
                                Cliente XMar868648241
                                Cliente XAbr861271711055
                                Cliente XMay861411004121
                                Cliente XJun86107297839

                                Here is more clear.

                                I only have information about Contracted and Did and I calculate the other columns:

                                 

                                Difference= Contracted - Did

                                Add next month = Difference * 0.5

                                To do= Contracted + Add next month (of the previous month) so To do (of April) = 86 + 41([Add next month] of March)

                                 

                                This table is ok, the results are ok, but if I select a month, my function above fails because I only have one row but I want to filter May and I want this result table:

                                 

                                ClienteMesContractedTo doDidDifferenceAdd next month
                                Cliente XMay861411004121

                                 

                                Instead

                                 

                                ClienteMesContractedTo doDidDifferenceAdd next month
                                Cliente XMay86861004121