How to calculate average with multiple conditions

[Tribhuwan]

Below is my dataset, where I need to calculate average of Amt basis below 2 criterias

1. Status = Won, Sales Members = Alex and Martha, SalesTeam1 = BDE1 and BDE2

2. Status = Won, Sales Members <> Alex and Martha, SalesTeam1 <> BDE1 and BDE2, Host = BCD

Second condition should exclude all the ID's that are already covered in first condition e.g. ID's 567779 and 573899 (these are not part of first calculation)

Would really appreciate if anyone can tell me the solution for this problem

ID	Amt	Status	Host	Close Year	SalesTeam1	Sales Members
352579	€19,896	Lost	ABC	2021	BDE1	Alex
352579	€13,260	Won	ABC	2022	BDE2	Martha
494308	€532,440	Won	BCD	2023	BDE1	John
494308	€18,960	Won	BCD	2022	Team4	Catherine
494308	€7,188	Won	BCD	2023	BDE1	Jonas
494308	€3,936	Won	BCD	2022	BDE2	Alex
543228	€5,640	Won	ABC	2023	Team3	Martha
553000	€6,181	Won	ABC	2022	Team4	John
554930	€10,404	Won	ABC	2023	BDE1	Catherine
561126	€43,440	Stopped	BCD	2022	BDE2	Jonas
562014	€11,016	Stopped	BCD	2023	Team3	Alex
564001	€3,936	Stopped	BCD	2023	Team4	Martha
566524	€206,453	Stopped	BCD	2023	BDE1	John
567452	€5,976	Won	BCD	2023	BDE2	Catherine
567779	€28,783	Won	BCD	2023	Team3	Jonas
567779	€120,000	Won	BCD	2023	Team4	Alex
571459	€15,156	Won	BCD	2023	BDE1	Martha
572776	€21,480	Won	BCD	2023	BDE2	John
573899	€116,760	Won	BCD	2023	Team3	Catherine
574905	€21,060	Won	EFG	2023	Team4	Jonas
574905	€16,380	Lost	EFG	2023	BDE1	Alex
574905	€10,908	Lost	EFG	2023	BDE2	Martha
574905	€5,064	Lost	EFG	2023	BDE1	John
575138	€6,300	Lost	EFG	2023	Team4	Catherine
576611	€11,928	Won	EFG	2023	BDE1	Jonas
576611	€377,911	Won	EFG	2023	BDE2	Alex
576611	€2,645	Won	EFG	2023	Team3	Martha
576611	€5,400	Won	EFG	2023	Team4	John
576611	€12,264	Won	EFG	2023	BDE1	Catherine

[BrunPierre]

=Avg({$<[BDE Flag]={1},Status={'WON'},Role={'BDE1','BDE2'},Year={2023}>} Amt)

[BrunPierre]

Perhaps, it's the currency sign. Try this the in script:

LOAD ID,
Amt,
Num(PurgeChar(Amt,'€')) as Amt,
...
FROM SourceTable;

[Tribhuwan]

Hi Mayil,

There is no output when I use this expression, just showing '-'

[Tribhuwan]

Hi,

The problem that I have is that I don't have BDE Flag in Qlik, I created it in excel but in Qlik I am getting data from multiple tables and there is no single table where I can add this flag.

Do you think is there any other way to get this output or the only option left is to create this flag in Qlik.

Please advise.

[BrunPierre]

Here's how to create the flag in the script.

[BDE NAMES]:
LOAD * Inline [
BDE TEAM
CLAIRE STRAITON
CALLUM FERGUSON
ARIAN MATZ
JEROEN VAN DEN BERG
EVELYN LIM ];

Data:
LOAD *,
If([BDE TEAM] = 1 and Match(Role,'BDE1','BDE2'),1,0) as [BDE Flag];

LOAD ID,
Status,
VP,
Date,
Year,
Amt,
Role,
Team_Name,
If(Exists([BDE TEAM],Team_Name),1,0) as [BDE TEAM]

FROM [...\Data_2023.xlsx] (ooxml, embedded labels, table is Sheet1);

DROP Table [BDE NAMES];

[Tribhuwan]

Hi

I have added the Flag now and used the below formula:

=Avg({$<[BDE Flag]={1},Status={'WON'},Role={'BDE1','BDE2'},Year={2023}>} Amt)

But my output is 12,599, I am using this formula in KPI, is there a problem with this

[BrunPierre]

This is my output. You might now need to share the application.

[Tribhuwan]

Thanks a lot buddy, it works for me now.

Could you please help me second set of expression and let me know your result

=Avg({$<[BDE Flag]={0},Status={'WON'},VP={'DZ'},Year={2023}>} Amt)

[BrunPierre · Accepted solution]

[Tribhuwan]

Thanks a lot for all the help and support in solving my problem