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