Hi All,
I Need help in using multiple if conditions for the below requirement.
Source data:
| TEAM | PROCESS | % Process |
| MLTV | Last Submit to First TV Checked | 39% |
| MLTV | Last Submit to First TV Action | 92% |
| LOAM | Final Approval to First Response | 97% |
| LOAM | Final Approval to LO Gen | 80% |
Requirement 1:
To Count Number of process in each teams & No of process that has more than 50% value. i achieved this using below expression
Output1:
| Team | Met Target |
| MLTV | 1/2 |
| LOAM | 2/2 |
Script1:
= if(GetSelectedCount(TEAM)=0,
sum(Aggr(
IF(count(DISTINCT{$<[END DT]={"*"},%ProcessingType={'End'},PROCESS={'*'}-{'First TV Pickup to First TV Checked By TVO'}, [SLA BUCKET]={'1'}>} [CASE_NUM]) /
count(DISTINCT{$<[END DT]={"*"},%ProcessingType={'End'},PROCESS={'*'}-{'First TV Pickup to First TV Checked By TVO'}>} [CASE_NUM])>=0.5,1,0),PROCESS))
& '/' & count( DISTINCT (IF(PROCESS<>'First TV Pickup to First TV Checked By TVO',PROCESS))))
,0))
Requirement 2:
To get the number of teams that has met 100% target in a text box
Output2 Needed:
1/2
out of 2 teams only LOAM Team has met 100% Target(2/2). so output should be 1/2.
Script2:
i used below script but my condition is applying condition on process level, below script is giving output as 0,because condition is applied at process level, since not all process met target its resulting 0.
=if(GetSelectedCount(TEAM)=0,
IF(sum(Aggr(
IF(count(DISTINCT{$<[END DT]={"*"},%ProcessingType={'End'},PROCESS={'*'}-{'First TV Pickup to First TV Checked By TVO'}, [SLA BUCKET]={'1'}>} [CASE_NUM]) /
count(DISTINCT{$<[END DT]={"*"},%ProcessingType={'End'},PROCESS={'*'}-{'First TV Pickup to First TV Checked By TVO'}>} [CASE_NUM])>=0.5,1,0),PROCESS))=
count( DISTINCT (IF(PROCESS<>'First TV Pickup to First TV Checked By TVO',PROCESS))),1,0))
Please Help with a solution.
Thanks In Advance.
Arjin.
