List box with expression

[#1]

Dear Community,

I want to display the list of country where the condition is media investment and revenue is greater than 0.

I tried with 2 expressions but not working.

Appreciate if you could help.

Expressions

1. =if([Media Investment] > 0 and Revenue > 0, Countrytarget)

2. = if([Media Investment] > 0, if([Revenue] > 0 , Countrytarget))

Thanks and best regards,

Chanel

[#2]

Hi

PFA Try this if it is wrong please try the below expression

=if([Media Investment] > 0 or Revenue > 0, Countrytarget)

oterwise please attach your sample app

[hic]

Your expressions will not work since you use unaggregated field references. Add aggregation functions, e.g.

=if(Sum([Media Investment]) > 0 and Sum(Revenue) > 0, Sum(Countrytarget))

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

HIC

[jonathandienst · Accepted solution]

Hi

This should work:

=Aggr(if([Media Investment] > 0 and Revenue > 0, Countrytarget), Countrytarget)

HTH

Jonathan

Edit: assumes that there is countrytarget, revenue and media investment have a 1:1:1 relationship. Otherwise , use sum() or ave() as per HC's post

[#5]

Hi Henric,

But it is working if i use :

=if( (Revenue) > 0, Countrytarget)

Thanks and best regards,
Chanel

[#6]

Thanks Jonathan and Henric Cronström!

It is working with combination of HC's post.

=Aggr(if(sum([Media Investment]) > 0 and sum(Revenue > 0), Countrytarget), Countrytarget)

Thanks and best regards,

Chanel