IF() function on bar chart

[#1]

Hi,

I am creating this post because I am meeting a strange issue with the function IF() in a bar chart.

Context :
I have a board with a column that gives me a a closing date. But the format isn't a date because there are lines where it is indicated "Non" instead when the project isn't over yet. I know how to change it into a date but I wanted to keep it as a text here.

I have another column that gives me the status of the stakeholder(client, supplier, internal).

Example :

Closing Date	Stakeholder
2017/05/02	Client
2017/05/12	Client
2017/07/30	Supplier
2017/08/22	Client
2017/09/14	Internal
Non	Client
Non	Client
Non	Client
Non	Supplier
Non	Internal

What I want is a stacked bar graph that gives me the number of projects with no closing date ("Non") for each stakeholder.

Because I didn't know how to do it on a stacked chart, I first tried to do a bar chart with 3 bars : client, internal and supplier, and the number of "Non" for each (3,1,1 with the example above).

So I put Stakeholder as the dimension and
IF([Closing Date]='Non', COUNT([Stakeholder])) as a measurement,
but it gives me no results !

I checked : IF([Closing Date]='Non', 14, COUNT([Stakeholder])) and it gives me the same results than COUNT([Stakeholder]).

So it looks like the condition [Closing Date]='Non' doesn't give any result.

What I find really strange is the fact that when I create a window "Text and image" with this formula (And a filter "Closing Date") :
IF([Closing Date]='Non',COUNT([Stakeholder]),'Cookies')

It gives me the COUNT([Stakeholder]) when I select 'Non' and 'Cookies' when I select something else.

In other words, the formula looks correct and works in the window "text and image" but not in a bar chart.

I am lost !

[sunny_talwar · Accepted solution]

Try this

Count({<[Closing Date]= {'Non'}>} [Stakeholder])

[#3]

It works perfectly ! Thanks !
If you have time to explain me why my formula didn't work, it would be great

[sunny_talwar]

This syntax is called set analysis, and basically we are saying that count only those rows where Closing Date = 'Non' and ignore every other row... The syntax can be studied more here:

A Primer on Set Analysis‌

and here

Why is it called Set Analysis?

[#5]

Thanks again, I'll check these links.

Have a nice day !

[Anonymous]

Sunny,

While your answer is perfectly correct, it makes sense to explain why IF() doesn't work here as written.

The problem here

IF([Closing Date]='Non', COUNT([Stakeholder]))

is that the condition is checked for multiple data rows, and it could be true for some rows but false for the others.  So it is not defined in most cases.

Here is a right way of using IF():

COUNT(if([Closing Date]='Non', [Stakeholder]))

Regards,

Michael