Simulating different possibilities using Qlikview

[santlal0589]

How to simulate in qlikview

Employees are tagged with their Achievement % in following bucket:

0%,0-20%,20-40%,40-60%,60-80%,80-100%,>100%

Below is actual count % in each category

User  want to increase count % in >100% bucket

let he enters 30%

extra Count%  will move from any lower bucket to >100%.

Let he set 3% in 80-100% , then 3.59% move from 80-100% to >100%

rest will move from 60-80%, In similar fashion he can set 60-80% bucket and move Count % from lower Bucket.

Complexity in Equation is increasing as there can be many possibilities. Also I need to give dump of Movement of employee from each category.

Currently I am getting required result below by using two variable for >100% and 80-100% bucket, how to show all posibilities

Kindly suggest any better way to approach the problem.

[sunny_talwar]

Would you be able to share what you have thus far?

[santlal0589]

i have used many if and  decreasing number in lower bucket.

[sunny_talwar]

i have used many if and  decreasing number in lower bucket.

?? Not sure what you mean?

[santlal0589]

in above case used variable v2 to set >100% bucket, and v1 for 80-100% bucket.

Assumed if 1 is there in above variable then there is no change

if(v2=1,(Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'>100%'}>}EMP_CODE)/

for >100%

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE)),v2)

for 80-100%

if( v2<>1 and v1=1,

if((v2-(Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'>100%'}>}EMP_CODE)/

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE)))>

(Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'80-100%'}>}EMP_CODE)/

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE)) and v2<>1,0,

((Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'80-100%'}>}EMP_CODE)/

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE))+

if(v2=1,0,((Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'>100%'}>}EMP_CODE)/

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE))-v2))

)),if(v1=1,(Count(DISTINCT {<Month_QtrFlag={1},DS_FTM_BWRP_FLAG={'80-100%'}>}EMP_CODE)/

Count(DISTINCT TOTAL{<Month_QtrFlag={1},[Hire Dt]={"<=$(=MonthEnd(AddMonths(Max(Date),-3)))"}>} EMP_CODE)),v1))