topic Re: Trouble with complex formula in a graph in QlikView

Trouble with complex formula in a graph

Anonymous — Mon, 22 Jun 2015 14:18:35 GMT

Hi everyone:

I'm having some trouble when putting a complex formula in a graph.

We have several programs and different types of users who can collaborate on them.

Users of type 1 can collaborate on several programs and can make it partially according to a proportion of time. They only collaborate in tasks 1 and 2.

Users type 2 can collaborate on only one program and make it full time (proportion=1). They only collaborate on task 3 and 4.

We want know how many users we have available for each task in such a way that if one user of type 1 or 2 sums more than 1 (sum of programs he/she can work in * proportion ) it counts like 1.

I am using this formula for users of type 1 (counting users for task1):

round(sum(if(aggr(sum( T1* proporcion), id_user )>=1, 1)) //greater than 1 counts 1

+ sum(if(aggr(sum( T1* proporcion), id_user )<1, //others what they sum

aggr(sum( T1* proporcion), id_user ))))

and it seems to work. But when I try to make a graph with a nested if it doesn't work properly. The fist expession of the nested if is ok, but the second (counting users for task2 ) is set to 0.

I attach an example. If you select task2 in the value list it shows the graph ok.

What is wrong? Can any body help me?

Thanks

PS: I have tried to load task 2 first in the inline table and it is the value that is shown. It seems to show the first value loaded.

Re: Trouble with complex formula in a graph

Anonymous — Mon, 22 Jun 2015 14:25:19 GMT

How about adding Task to your Aggr() 's.

if(Task ='T1',

round(sum(if(aggr(sum( T1* proporcion), id_user, Task )>=1, 1)) //los que suman más de 1 se quedan a 1

+ sum(if(aggr(sum( T1* proporcion), id_user, Task )<1, //los que suman menos de 1 a lo que suman

aggr(sum( T1* proporcion), id_user )))),

if(Task='T2',

round(sum(if(aggr(sum( T2* proporcion), id_user, Task )>=1, 1)) //los que suman más de 1 se quedan a 1

+ sum(if(aggr(sum( T2* proporcion), id_user, Task )<1, //los que suman menos de 1 a lo que suman

aggr(sum( T2* proporcion), id_user )))),

if(Task='T3', sum(T3),

if(Task='T4', sum(T4),

))))

Re: Trouble with complex formula in a graph

Anonymous — Mon, 22 Jun 2015 15:35:25 GMT

Ok, It works!!!

Thank you very much!