Set analisys & slider

[aisolomatin]

Hi!

I'm stucked with issue and hope somebody helps me.

What we get:

1. Company with Branches, Divisions and Subdivisions;

2. Data about staff: number of Regular staff, number of Vacancies;

3. Time period: 2 months.

What we need to know:

1. How many Subdivisions/Divisions/Branches achived the plan for %_staffing (98%).

Formula for %_staffing = 1-Vacancies/Regular staff.

We got Regular_staff slider for flexibile subdivisions filtration.

I've managed with Subdivisions (right panel).

For example: if we set up Slider to 0, the numer of subdivisions that achieved the plan will be 8, not achieved - 1.

If slider will be set up at 100, the numer of subdivisions that achieved the plan will be 4, not achieved - 0. (we don't need to consider empty Subdivisions in number of Divisions).

But I don't know how manage with Divisions and Branches.

[aisolomatin]

Hi, Settu!

Thank you for your answer, but it's not exactly what I need.

In your version, if we set up slider at 100, the right pannel will be looks like:

We have 4 Divisions that achived plan, and 0 that not.

But I want the logic works like this:

And the answer should be: 3 Divisions achived plan, 4 not.

I tried to exclude from your expression ({<Subdivision-={""}>}, but it doesn't helps.

[vinieme12]

How is it that 0 divisions not achieved the plan?

In the Table you can see Division 25 and No_Division are less than 98% so the correct answer for division should be 4 and 2 at 0 staffing and 4,1 at 100 because no_division is 265??

or am i reading the table wrong??

Expression

MET =

sum(Aggr(if(Sum(Reg_staff)>=v_min_staff,

if((1-sum(Vac)/sum(Reg_staff))>=0.98,1,0),0),Division_code))

and

Not_Met =

=sum(Aggr(if(Sum(Reg_staff)>=v_min_staff,

if((1-sum(Vac)/sum(Reg_staff))<0.98,1,0),0),Division_code))

[aisolomatin]

We have 7 Divisions in total : Division_11,12,23,24,25 + No_division+No_division (they concerns to different branches, so we have to consider them separatly).

If we exclude all subdivisions from divisions, than have Regular_staff>100, we will have the result like on my picture: 3 divisions achived plan, 4 not.

[vinieme12]

the problem is your division_code doesn't identify branch1 - No_division and Branch2_No_Division separately

so you are only aggregating over 6 distinct division codes instead of 7

create a unique division code example 1_11,1_12,1_No_Div for branch1 divisions and

2_23,2_24,2_25,2_No_Div

[vinieme12 · Accepted solution]

for Divisions

=sum(AGGR(

if(

1-sum(TOTAL <Division,Subdivision>

AGGR(

if((Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))>v_min_staff, Sum(TOTAL <Branch,Division,Subdivision> Vac))

,Branch,Division,Subdivision)

)

/

sum(TOTAL <Division,Subdivision>

AGGR(

if((Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))>v_min_staff, Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))

,Branch,Division,Subdivision)

)

>=0.98,1,0)

,

Branch,Division_code,Subdivision))

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

=sum(AGGR(

if(

1-sum(TOTAL <Division,Subdivision>

AGGR(

if((Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))>v_min_staff, Sum(TOTAL <Branch,Division,Subdivision> Vac))

,Branch,Division,Subdivision)

)

/

sum(TOTAL <Division,Subdivision>

AGGR(

if((Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))>v_min_staff, Sum(TOTAL <Branch,Division,Subdivision> Reg_staff))

,Branch,Division,Subdivision)

)

<0.98,1,0)

,

Branch,Division_code,Subdivision))

Proceed in similar manner for Branches , let me know if you need help

[aisolomatin]

Thank you, Rineeth! Thats the thing!

[aisolomatin]

Vineeth, Hi!

i got some new terms with this issue and I need help.

Could I to proceed this topic or better to start a new one?

[vinieme12]

Hi,

Start a new thread as it will sort to top by created date and so other community members can also look into it.

[aisolomatin]

!

Good day.

Thank you for previous help.

I got new terms for this issue and i need your advice.

What we get:

1. Company with Branches, Divisions and Subdivisions;

2. Data about staff: number of Regular staff, number of Vacancies;

3. Time period: 2 months.

What we need to know:

1. How many Subdivisions/Divisions/Branches achived the plan for %_staffing (98%).

Formula for %_staffing = 1-Vacancies/Regular staff.

We got Staff_in_Subdivision and Vacancies_in_Subdivision sliders for flexibile subdivisions filtration.

For Date="01.01.2016"

If we set up Staff slider to 120, and Vacancies slider to 2, then we should get next result.

We get rid of all Subdivisions, that have staff<120 and Vac<2.

And new condition: if Division have only "n_a" Subdivision that fit to our condition (Staff>=120 and Vac>=2), then we don't need to count it (Division 22).