Different from 0 in set analysis

[maxim1500]

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!

[maxim1500]

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

[sunny_talwar]

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

[maxim1500]

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

[maxim1500]

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.

[sunny_talwar]

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

[maxim1500]

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

[swuehl · Accepted solution]

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.

[maxim1500]

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

[sunny_talwar]

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

Best,

Sunny

[swuehl]

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?).