Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Venkatsj
Contributor III
Contributor III

Average with Multiple conditions

Hi,

Need help with the expression

Name Status Days
A Distribution 3
A Minor 5
B Distribution 5
B Minor 5
G Vacation 3
D Tour 2
E Distribution 2

 

From the above table I wanted to calculate average number of days using "Days" Column. But using filter in Column "Name" = A, "Status" = Distribution & Minor. 

Please guide, Thank you.

14 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Something like below ?

Avg({<Name = {'A'}, Status = {'Distribution ','Minor'}>}Days)

 

Venkatsj
Contributor III
Contributor III
Author

Thank you so much Ashutosh, it helped me a lot. One small doubt in case in that expression inside "Status" if i want to filter the number from 1 to 5 how can i write expression?

For e.g. 

Avg({<Name = {'A'}, Status = {'>=0<=5'}>}Days)

is it right ?

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Hello,

You mean filtering Days (0 to 5) ??

You can add one more set in the expression like below.

Avg({<Name = {'A'}, Status = {'Distribution','Minor'},Days = {">=0<=5"}>}Days)

 

Thanks,

Ashutosh

Venkatsj
Contributor III
Contributor III
Author

Thanks you ashutosh. Helped a lot.

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Great 🙂

Tribhuwan
Contributor
Contributor

Hi Ashutosh,

 

How can we use does not equal in this formula?

Example

Avg({<Name = {'A'}, Status <> {'Distribution ','Minor'}>}Days)

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Hi,

Use -= instead of <>

E.g Status -= {'Distribution', 'Minor'}

Tribhuwan
Contributor
Contributor

Thanks a lot Ashutosh,

Can I use aggregate or distinct with this formula as I have duplicate data points

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Yes.