topic Re: Writing Multiple IF Condition with Sum aggregate function in QlikView

Writing Multiple IF Condition with Sum aggregate function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I Need help with below requirement.

Data:

Team	Process	Actual	Total	Standard
MTV	ECOM	40	100	50%
MTV	DTCKT	60	90	50%
LOAM	LOTICKT	90	160	50%
LOAM	LODRAFT	120	200	50%

Requirement : Want to Create a text box to show number of teams that met standard (i need the output in team level).

Condition: If 2 process of a team meets Standard( Actual/Total>=50%) then Teamstd count will be 1, else if only 1 team meets standard(>=50%) then it will be Teamstd counted as 0.

Explanation : In the above table for TEAM="MTV" only 1 team [ DTCKT Actual/Total >=50] where as in TEAM="LOAM" for 2 process Actual/Total <=50% so in the text box i want to show 1/2 (it says only 1 team out of 2 has 100% Success rate).

Please help.

Thanks in advance.

Arjin.

Re: Writing Multiple IF Condition with Sum aggregate function

Anil_Babu_Samineni — Fri, 30 Sep 2016 16:44:25 GMT

Perhaps you required from Script level

If(Team = 'MTV', (Sum({<Process = {'DTCKT'}>}Actual)/Sum(TOTAL Actual)) >= '50', (Sum({<TEAM = {'LOAM'}, Process = P::$Process>}Actual)/Sum(TOTAL Actual)) <= '50' )))

Re: Writing Multiple IF Condition with Sum aggregate function

sunny_talwar — Fri, 30 Sep 2016 16:51:02 GMT

Look here:

Writing Multiple IF Condition with Sum aggregate function

Re: Writing Multiple IF Condition with Sum aggregate function

Anonymous — Mon, 03 Oct 2016 08:34:29 GMT

Hi Sunny,

I am facing issue in context to above and request your kind help to look into it,

My actual data has 5 Teams where as only 2 teams has Process (MTV & LOAM)

Team	Process	Actual	Total
MTV	ECOM	40	100
MTV	DTCKT	60	90
LOAM	LOTICKT	90	160
LOAM	LODRAFT	120	200
LS
LD
RDM
DIDB

When i use the below expression i am getting the result as 0 where as it should be 1, applying if(Team='MTV' or Team='LOAM',Team) is skewing the result, Please help to fix this.

=Sum(Aggr(If(Sum(Aggr(If(Actual/Total >= 0.50, 1, 0), Process,if(Team='MTV' or Team='LOAM',Team))) = Count(Process), 1, 0), if(Team='MTV' or Team='LOAM',Team)))

Thanks in advance,

Arjin.

Re: Writing Multiple IF Condition with Sum aggregate function

sunny_talwar — Mon, 03 Oct 2016 17:45:25 GMT

Would you be able to share a sample to look at the issue here?