Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF() function on bar chart

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 DateStakeholder
2017/05/02Client
2017/05/12Client
2017/07/30Supplier
2017/08/22Client
2017/09/14Internal
NonClient
NonClient
NonClient
NonSupplier
NonInternal

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

2017-09-11 13_50_52-RNC Qualité TABLEAUX_DE_BORD1 - Excel.png

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 !

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

5 Replies
sunny_talwar

Try this

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

Not applicable
Author

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?

Not applicable
Author

Thanks again, I'll check these links.

Have a nice day !

Anonymous
Not applicable
Author

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