Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Set analisys & slider

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

aisolomatin

Contributor III

2016-11-24
11:18 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Set analisys & slider

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.

1 Solution

Accepted Solutions

vinieme12

Champion III

2016-11-25
05:04 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

639 Views

9 Replies

aisolomatin

Contributor III

2016-11-25
01:46 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

639 Views

vinieme12

Champion III

2016-11-25
02:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

639 Views

aisolomatin

Contributor III

2016-11-25
02:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

639 Views

vinieme12

Champion III

2016-11-25
02:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

639 Views

vinieme12

Champion III

2016-11-25
05:04 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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(

,Branch,Division,Subdivision)

)

/

sum(TOTAL <Division,Subdivision>

AGGR(

,Branch,Division,Subdivision)

)

<0.98,1,0)

,

Branch,Division_code,Subdivision))

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

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

640 Views

aisolomatin

Contributor III

2016-11-26
05:18 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you, Rineeth! Thats the thing!

639 Views

aisolomatin

Contributor III

2016-12-14
06:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

639 Views

vinieme12

Champion III

2016-12-14
06:50 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

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

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

639 Views

aisolomatin

Contributor III

2016-12-14
09:23 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

!

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).

639 Views