results using above

[chematos]

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)

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:

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:

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.

[chematos]

Something??

[giakoum]

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

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

[chematos]

Yes, Cliente and Mes are dimensions.

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

Thank you for your response

[giakoum]

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.

[chematos]

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

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

[giakoum]

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.

[chematos]

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

Instead

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