22 Replies Latest reply: Dec 15, 2016 10:01 AM by Stefan Wühl

# 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!

• ###### 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])

• ###### 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.

• ###### Re: Different from 0 in set analysis

then try greater than 0

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

• ###### 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'}

• ###### 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

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

• ###### 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

• ###### Re: Different from 0 in set analysis

Don't know why, but numbers are off... Looks logical though...

• ###### Re: Different from 0 in set analysis

May be you have more than one value and/or quantity for each security id?

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

• ###### Re: Different from 0 in set analysis

Sadly, it doesn't solve the issue...

In fact, it should be

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

since I want securities that have one of the conditions true. But still wrong...

• ###### Re: Different from 0 in set analysis

Even this :

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

doesn't give the right number, if I consider only the quantity part. Sum and Num gives the same result.

• ###### Re: Different from 0 in set analysis

Its difficult to see what might be causing this, would you be able to share your application to take a quick look at it?

• ###### Re: Different from 0 in set analysis

Thanks for your help Sunny, but I cannot publish the app for security reasons, even without data.

• ###### 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.

• ###### Re: Different from 0 in set analysis

That's right! Thanks for your help guys!

The only little correction needed is to replace the " by ', and it works fine. Here is the working solution:

Count({<[Security ID] = {'=Only({<\$(=vSelectedDate)>} [Position Quantity]) <> 0 or Only({<\$(=vSelectedDate)>} [Position Total Value]) <> 0'}>} DISTINCT [Security ID])

I am wondering though, is it faster to use this with two only or use something like this:

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

• ###### Re: Different from 0 in set analysis

Wow!! We were so close and yet so far away.....Thanks to Stefan for taking us all the way.

Best,

Sunny

• ###### Re: Different from 0 in set analysis

I am wondering though, is it faster to use this with two only or use something like this:

You'll need to test this with your real data, I think (but wasn't there a problem with filtering on zero?).

• ###### Re: Different from 0 in set analysis

I fixed that with Sunny's help. Simply added a num([Position Quantity] as [Position Quantity] in load script. I'll try it out with the full dataset and let you know, Thanks again.

• ###### Re: Different from 0 in set analysis

Looks like this one is faster:

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

Not by much, but on my dataset, this one shows faster every time.

• ###### Re: Different from 0 in set analysis

Could very well be the case.

It all depends on your data, e.g. how many disting symbols you need to filter in the set modifier.

Also the advanced search has a little overhead by creating a hypercube.

So it's best to test several approaches.