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: 
Applicable88
Creator III
Creator III

If-function within a count/ and other function

Hello!

I'm beginner here, can someone explain me in simple words what this formula actually does:

count(distinct if([Orders.all] = '0', Null(), [Material.all]))

and also is there an alternative to write this different with other formular to get same results?

 

Thank you in advance 

 

3 Solutions

Accepted Solutions
marcus_sommer

Better would probably be: count({< [Orders.all] -= {0}>} distinct [Material.all]) 

The meaning is to count the material without orders with the value of 0.

- Marcus

View solution in original post

jensmunnichs
Creator III
Creator III

The IF formula will check if the 'Orders.all' is 0, and if it is not the 'Material.all' is returned. The count(DISTINCT x) then counts the amount of unique values in the list of values returned by the IF function. In other words, you are counting the amount of unique Material.all values that have 0 Orders.all.

A different (and probably better) way to write this is to use set analysis. Set analysis is a way of limiting the set you are analyzing with a function. In this case, that would be: =COUNT({<[Orders.all]-={0}>} DISTINCT [Material.all]). The set analysis part (marked red) will limit the data set to all lines where Orders.all is not 0, and then it's just a matter of a simple COUNT(DISTINCT x) function to get the result we want.

You might want to familiarize yourself with Set Analysis, as this is a very important part of creating Qlikview/Qlik Sense applications. A good place to start would be here.

View solution in original post

jensmunnichs
Creator III
Creator III


Shouldnt't it be like: In other words, you are counting the amount of unique Material.all values that have NOT 0 Orders.all?


Yes you're absolutely right about this, my mistake. The formula using set analysis in my original post is still correct though.

About your second question, I think set analysis is just faster than an if-statement (performance wise), and it's a bit easier to read. I also think that, once you're used to using set analysis, it's a lot easier to code with than using IF-statements.

There are probably better reasons out there, I'm sure if you do some Googling you'll find some explanations from people much smarter than I am. For example,  Jagan's post here. 

View solution in original post

4 Replies
marcus_sommer

Better would probably be: count({< [Orders.all] -= {0}>} distinct [Material.all]) 

The meaning is to count the material without orders with the value of 0.

- Marcus

jensmunnichs
Creator III
Creator III

The IF formula will check if the 'Orders.all' is 0, and if it is not the 'Material.all' is returned. The count(DISTINCT x) then counts the amount of unique values in the list of values returned by the IF function. In other words, you are counting the amount of unique Material.all values that have 0 Orders.all.

A different (and probably better) way to write this is to use set analysis. Set analysis is a way of limiting the set you are analyzing with a function. In this case, that would be: =COUNT({<[Orders.all]-={0}>} DISTINCT [Material.all]). The set analysis part (marked red) will limit the data set to all lines where Orders.all is not 0, and then it's just a matter of a simple COUNT(DISTINCT x) function to get the result we want.

You might want to familiarize yourself with Set Analysis, as this is a very important part of creating Qlikview/Qlik Sense applications. A good place to start would be here.

Applicable88
Creator III
Creator III
Author

Hi Jens,

thank both of you and Marcus as well. 

But your explanation of the first expression of mine got me confused. You wrote:  "In other words, you are counting the amount of unique Material.all values that have 0 Orders.all."

Shouldnt't it be like: In other words, you are counting the amount of unique Material.all values that have NOT 0 Orders.all?

Second question....so I actually heard often that the  most elegant way if possible is always the usage of "set analysis" instead of a another function like "if". Like the one I mentioned first. I read the content of your link. But can you still go further in explaining why it is so much better than the first solution?

Much appreciated.

Best regards.

 

 

jensmunnichs
Creator III
Creator III


Shouldnt't it be like: In other words, you are counting the amount of unique Material.all values that have NOT 0 Orders.all?


Yes you're absolutely right about this, my mistake. The formula using set analysis in my original post is still correct though.

About your second question, I think set analysis is just faster than an if-statement (performance wise), and it's a bit easier to read. I also think that, once you're used to using set analysis, it's a lot easier to code with than using IF-statements.

There are probably better reasons out there, I'm sure if you do some Googling you'll find some explanations from people much smarter than I am. For example,  Jagan's post here.