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

    Different from 0 in set analysis

    Maxime Dumas

      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!