Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: If-function within a count/ and other function

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Applicable88

Creator III

2020-03-25
07:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

568 Views

3 Solutions

Accepted Solutions

marcus_sommer

MVP & Luminary

2020-03-25
08:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

2020-03-25
08:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

jensmunnichs

Creator III

2020-03-25
05:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

NOT0 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.

4 Replies

marcus_sommer

MVP & Luminary

2020-03-25
08:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

2020-03-25
08:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

2020-03-25
03:09 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

510 Views

jensmunnichs

Creator III

2020-03-25
05:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

NOT0 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.