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]

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.

[maxim1500]

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.

[swuehl]

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.

[gerald_lebret]

Hello,

I know this post is a bit old but I am having a bit of an issue.

I have some formulas where I use set analysis. In the set analysis, I need to exclude 0 value from one or more dimension but my problem is that is I enter -={'0'} I have a result and If I enter = {'<>0'} I end up with another one.

It seems like in some case the first solution applpies and in other case it is the second.

Which one is the correct one?

Thank you very much for your help.