topic help with formula in QlikView

help with formula

Mon, 10 Oct 2011 17:22:48 GMT

hi all.

i am looking for a formula to determine the achievement in percentage for the following situation:

- i have a number of groups that have a number of members. These groups are of certain types. This is stored in the following 2 tables:

groups:

groupid	grouptype
1	basic
2	silver
3	gold

members:

memberid	groupid	membername
1	1	member1
2	1	member2
3	1	member3
4	2	member4
5	2	member5
6	2	member6
7	3	member7
8	3	member8

depending on the type of the group, a group has certain duties

	basic	silver	gold
take exam A	1	2	2
take exam B	1	2	2
	2 exams passed by min. of 2 members	4 exams passed by min. 3 members	4 exams passed by min. 2 members

i embedded these duties in the group table:

groups:

groupid	grouptype	examA	examB	minIndividuals
1	basic	1	1	2
2	silver	2	2	3
3	gold	2	2	2

Now i also have stored all attempts of the exams. some passed, some failed.

what i want: i want to calculate and display the % achievement of exams for

each group depending on their "duties". means: if group gold sends one and the same member to exams A and B 2 times each and this member passes each time, it must not be 100% achievement, but only 50%...

I hope, the scenario and the requirement became clear.

i appreciate any hints and ideas.

Regards,

help with formula

swuehl — Mon, 10 Oct 2011 18:27:34 GMT

Hi,

how do you define that you will get 50% and not e.g. 75% in your example?

If I understood you correctly, if any other team member passes either exam A or B, you will have 4 exams passed by min. 2 members, so up to now there is only 1 out of total 4 exams missing, right?

(Just for clarification: is it possible, that one team member passes same exam twice or does this count only for one pass? To me, letting a single team passing a single exam more once doesn't make sense.)

Could you also add some sample data for passing / failing exams per memberid? That would just make playing around a little bit easier.

Regards,

Stefan

Re: help with formula

Mon, 10 Oct 2011 19:25:47 GMT

hi stefan.

I try to clarify this:

you are right: achievement would be 75%, not 50, because team members are allowed to do the same exam twice. i have set up a different duty table:

groupid grouptype examA examB minIndividuals

1 basic 1 1 2

2 silver 2 2 4

3 gold 2 2 3

...well - and at this point, following up on my example above but respecting the new duty set up, i dont know what is the % achievement of group gold. i will add a sample file tomorrow. Hopefully so far the problem became a bit clearer..

I added a little sample file to play around with.

thanks a lot & regards,