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]

Hi, @Tribhuwan for instance, these will show the average yearly sales amount per sales member.

1. Avg(Aggr(Sum({$<Status={'Won'},[Sales Members]={'Alex','Martha'},SalesTeam1={'BDE1','BDE2'}>} Amt) [Close Year], [Sales Members]))

2. Avg(Aggr(Sum({$<Status={'Won'},[Sales Members]-={'Alex','Martha'},SalesTeam1-={'BDE1','BDE2'},Host={'BCD'}>} Amt) [Close Year], [Sales Members]))

[Tribhuwan]

Hi @BrunPierre 

When I used the below formula, its just showing as "-"

Avg(Aggr(Sum({$<Status={'Won'},[Sales Members]={'Alex','Martha'},SalesTeam1={'BDE1','BDE2'}>} Amt) [Close Year], [Sales Members]))

Also I need to add Close Year=2023

Could you please let me know what I am doing wrong here

Basically I want the the average Amt for Close Year=2023 but as per the below criteria

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

[BrunPierre]

Missing comma, as below.

Avg(Aggr(Sum({$<Status={'Won'},[Sales Members]={'Alex','Martha'},SalesTeam1={'BDE1','BDE2'},[Close Year]={'2023'}>} Amount),[Close Year],[Sales Members]))

[Tribhuwan]

No its showing 0.

Let me try to explain again my problem, if you go by my requirement only one data point qualifies the criteria (highlighted in orange):

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

but while calculating average I need to consider all records for ID - 576611, so the average should be 82,030

I can do this calculation in excel but don't know whether its possible in Qliksense or not.

[MayilVahanan]

Hi

Based on the sample data, what is ur expected output ?

[Tribhuwan]

Hi

Its should be 82,030 as only one ID (576611) qualifies all the criteria 

[MayilVahanan]

Hi

For me, its getting 2 Ids: 571459 & 576611. 

Could you try this expression and inform any other conditions are required to include?

Avg({<ID = P({<Status = {'Won'}, [Sales Members] = {'Alex','Martha'}, SalesTeam1 = {'BDE1','BDE2'}, [Close Year]={'2023'}>}ID)>*
<ID = e({<Status = {'Won'}, [Sales Members] -= {'Alex','Martha'}, SalesTeam1 -= {'BDE1','BDE2'}, Host={'BCD'}, [Close Year]={'2023'}>}ID)>}Amt)

[BrunPierre]

I misunderstood the requirement. So essentially condition 1 minus condition 2, but having met the criteria the results rather has other records.

=Avg( {$<ID = P({$<Status={'Won'},[Sales Members]={'Alex','Martha'},SalesTeam1={'BDE1','BDE2'},[Close Year]={'2023'}>})> - <ID = P({<Status={'Won'},[Sales Members]-={'Alex','Martha'},SalesTeam1-={'BDE1','BDE2'},Host={'BCD'},[Close Year]={'2023'}>})>}Amt)

[Tribhuwan]

Not getting any value. I have attached excel file with the dataset I am using and pivot to get the avergae that I need.

I have created 2 flags in the file, 

- First BDE TEAM wherever we have names CLAIRE STRAITON', 'CALLUM FERGUSON', 'ARIAN MATZ', 'JEROEN VAN DEN BERG', 'EVELYN LIM'

- Second Flag , if any ID has above Team_Name and Role as BDE1 and BDE2, than all ID's flageed as 1

Than I used pivot to get the desired numbers using flags.

So is it a way to get the desired result using formula in Qlik