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

# results using above

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)

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

 Cliente Mes Contracted To do Did Difference Add next month Client X Mar 86 86 4 82 41 Client X Apr 86 127 17 110 55 Client X May 86 141 100 41 21 Client X Jun 86 107 29 78 39

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

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

 Cliente Mes Contracted To do Did (To do)-(Did) Add next month Cliente X May 86 86 100 -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

Something??

• ###### Re: results using above

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

Yes, Cliente and Mes are dimensions.

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

• ###### Re: results using above

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

Hi,

This would be my data:

 Cliente Mes Contracted Did Client X Mar 86 4 Client X Abr 86 17 Client X May 86 100 Client X Jun 86 29

So the other columns are calculated from these two columns.

The results of my table are ok:

 To do 86 127 141 107

 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

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
 Cliente Mes Contracted To do Did Difference Add next month Cliente X Mar 86 86 4 82 41 Cliente X Abr 86 127 17 110 55 Cliente X May 86 141 100 41 21 Cliente X Jun 86 107 29 78 39

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:

 Cliente Mes Contracted To do Did Difference Add next month Cliente X May 86 141 100 41 21

 Cliente Mes Contracted To do Did Difference Add next month Cliente X May 86 86 100 41 21