Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Tribhuwan
Contributor
Contributor

How to calculate average with multiple conditions

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

 

Labels (1)
1 Solution

Accepted Solutions
19 Replies
BrunPierre
Partner - Master
Partner - Master

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
Contributor
Contributor
Author

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
Partner - Master
Partner - Master

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
Contributor
Contributor
Author

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 ?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Tribhuwan
Contributor
Contributor
Author

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
BrunPierre
Partner - Master
Partner - Master

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)

BrunPierre_0-1687505341153.png

Tribhuwan
Contributor
Contributor
Author

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