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!
Don't know why, but numbers are off... Looks logical though...
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])
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...
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.
Its difficult to see what might be causing this, would you be able to share your application to take a quick look at it?
Thanks for your help Sunny, but I cannot publish the app for security reasons, even without data.
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.
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])
Wow!! We were so close and yet so far away.....Thanks to Stefan for taking us all the way.
Best,
Sunny
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?).