topic Re: results using above in QlikView

results using above

chematos — Thu, 14 Jun 2012 19:33:02 GMT

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.

Re: results using above

chematos — Fri, 15 Jun 2012 17:24:35 GMT

Something??

Re: results using above

giakoum — Fri, 15 Jun 2012 18:01:51 GMT

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

chematos — Fri, 15 Jun 2012 18:16:29 GMT

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

giakoum — Fri, 15 Jun 2012 18:50:27 GMT

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

chematos — Fri, 15 Jun 2012 20:15:58 GMT

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

127

141

107

Your column is:

Cumulative

75.5

68.5

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

giakoum — Fri, 15 Jun 2012 20:35:11 GMT

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

chematos — Fri, 15 Jun 2012 20:54:19 GMT

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