Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

maxim1500
Contributor

Different from 0 in set analysis

Hi,

I am trying to count the number of ids from a fact table where the quantity or total value is different from 0 . Both fields are in the fact table. The following query works fine:

Count({<$(=vSelectedDate), [Position Total Value] = {'<0'}+{'>0'}>+<$(=vSelectedDate), [Position Quantity] = {'<0'}+{'>0'}>}distinct [Security ID])

I was wondering if there were better ways to do this. For example, I tried to merge {'<0'}+{'>0'} into a single condition, such as:

Count({<$(=vSelectedDate), [Position Total Value] -= {'0'}>+<$(=vSelectedDate), [Position Quantity] -= {'0'}>}distinct [Security ID])

But the result is wrong. As a matter of fact, the following query returns 0, and there are >100.

Count({<$(=vSelectedDate), [Position Quantity] = {'0'}>}distinct [Security ID]).

Any idea how to do better? We have some performance issues, and we hope to improve that by simplifying set analysis as much as possible.

Thanks!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Different from 0 in set analysis

Maybe you need to use your date filter also in the advanced search:

Count({<$(=vSelectedDate), [Security ID] = {"=Only({<$(=vSelectedDate)>} [Position Quantity]) <> 0"}>} DISTINCT [Security ID])


(maybe you don't need the first part then:

Count({< [Security ID] = {"=Only({<$(=vSelectedDate)>} [Position Quantity]) <> 0"}>} DISTINCT [Security ID])


Not sure if your dollar sign expansion is correct, that's hard to tell with the few information provided.

22 Replies

Re: Different from 0 in set analysis

Try intersection of negatives instead of union:

Count({<$(=vSelectedDate), [Position Total Value] -= {'0'}, [Position Quantity] -= {'0'}>}distinct [Security ID])

maxim1500
Contributor

Re: Different from 0 in set analysis

Thanks Sunny,

For some reason, [Position Quantity] = {'0'} does not work. Data is numeric and in the fact table. This is not a dimension. I tried removing the comas, adding =0 with or without comas, doesn't work. I checked manually in the dataset, and there are values at 0.00000000 as a quantity.

vinieme12
Esteemed Contributor II

Re: Different from 0 in set analysis

then try greater than 0

Count({<$(=vSelectedDate), [Position Total Value] = {'>0'}, [Position Quantity] = {'>0'}>}distinct [Security ID])

maxim1500
Contributor

Re: Different from 0 in set analysis

Quantity could be negative. That's why I used {'<0'}+{'>0'}

Re: Different from 0 in set analysis

Are you trying to include of exclude 0?

[Position Quantity] -= {'0'} or [Position Quantity] = {'0'}

maxim1500
Contributor

Re: Different from 0 in set analysis

I want to exclude it. But both of them do not work. If I exclude, it returns the full dataset. If I include, it returns 0.

Re: Different from 0 in set analysis

How about this?

[Position Quantity] = {"=Num([Position Quantity]) <> 0"}

maxim1500
Contributor

Re: Different from 0 in set analysis

Yeah that works. I guess there is something wrong with format in my table. Thanks!

The idea of [Position Total Value] -= {'0'}, [Position Quantity] -= {'0'} won't work though. I need to exclude entries that have both total value and quantity = 0 at the same time. If you have one or the other, it needs to stay.

Re: Different from 0 in set analysis

Got it, then back may be like this:

Count({<$(=vSelectedDate), [Security ID] = {"=[Position Total Value] <> 0 and Num([Position Quantity]) <> 0"}>} DISTINCT [Security ID])

Pick Security ID where both Value and Quantity are not equal to 0

Community Browser