Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Try intersection of negatives instead of union:
Count({<$(=vSelectedDate), [Position Total Value] -= {'0'}, [Position Quantity] -= {'0'}>}distinct [Security ID])
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.
then try greater than 0
Count({<$(=vSelectedDate), [Position Total Value] = {'>0'}, [Position Quantity] = {'>0'}>}distinct [Security ID])
Quantity could be negative. That's why I used {'<0'}+{'>0'}
Are you trying to include of exclude 0?
[Position Quantity] -= {'0'} or [Position Quantity] = {'0'}
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.
How about this?
[Position Quantity] = {"=Num([Position Quantity]) <> 0"}
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.
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