9 Replies Latest reply on Oct 26, 2018 6:12 AM by tanvi khanna

# IF statement with And condition not working.

Hi All,

I'm testing If And condition with a calculated field but couldn't get it to work. I have link my file below.

So for each customer(customer ID) I want to determined if they have purchased during year 2011 and 2012, if its true return a 1.

My calculated field goes like this:

IF YEAR([Order Date]) = 2011 AND  YEAR([Order Date]) = 2012 THEN 1

end

As you can see for Customer ID 6 he have purchased both on 2011 and 2012 but the calculated field above is reflecting 0 instead of 1.

• ###### 1. Re: IF statement with And condition not working.

Hi, Lim

Hope this helps

ZZ

• ###### 2. Re: IF statement with And condition not working.

But I need it to be 1 when customer purchase on 2011 AND 2012 not 2011 OR 2012.

• ###### 3. Re: IF statement with And condition not working.

Hi, Lim

Hope this helps

ZZ

• ###### 4. Re: IF statement with And condition not working.

Thanks Zhouyi.

That seem to work but I have a question.

"IF YEAR([Order Date]) = 2011 AND  YEAR([Order Date]) = 2012 THEN 1

end"

Why does the AND not work here?

• ###### 5. Re: IF statement with And condition not working.

Hi, Lim

ZZ

1 of 1 people found this helpful
• ###### 6. Re: IF statement with And condition not working.

Thank you very much. This really help me understand the condition and the structure of the data and logic. Appreciate your help

• ###### 7. Re: IF statement with And condition not working.

Hi Zhouyi Zhang,

I had a similar situation and a bit urgent - let me know if you understand what I ask -

I have a map chart below which is a filter for country/business group =Asia, Canada, USA

Map chart (left) controls a heat chart (right) that is a table with geography hierarchy: country/business group ->Units (belonging to each country/business group)

Ques: 1) when user does not select anything on map chart or RESET ie. all 3 groups are active -> the heat chart should show the country/business groups:

2)when the user selects one of the country/business group (eg ASIA), the heat chart should show a level below: units for that country:

I tried several 'if' & 'and' condition -> but dint work.

The current functionality is below. I am trying to create the situation where user does not have to click on the drill down button, but as soon as selects country group, it should display units (the col should become units when country is selected else should continue to be groups)

Tried this as well:

if ([Country (group)]="Canada" and [Country (group)]!="USA" and [Country (group)]!="Asia")

elseif ([Country (group)]="USA" and [Country (group)]!="Asia" and [Country (group)]!="Canada")

elseif  ([Country (group)]="Asia" and [Country (group)]!="Canada" and [Country (group)]!="USA")

END

Thanks in advance! any urgent help is appreciated...

Tanvi

• ###### 8. Re: IF statement with And condition not working.

Hi, Tanvi

There is possible way if user only select one region at a time, but it depends on what your data structure looks like and how you build the view. If you could share a sample workbook which reflect your issue, will be easier to further look into it.

ZZ

• ###### 9. Re: IF statement with And condition not working.

thanks for answering. my data is a flat file.

I HAVE created a dummy data set and dashboard explaining exactly for what I am looking for.

Please find attached in the previous reply (I am not able to attach in this reply). Any urgent help is much appreciated.

Thanks!