9 Replies Latest reply: May 6, 2016 4:00 PM by Sunny Talwar RSS

    Counting securities associated with 3 or less accounts

    Maxime Dumas

      Hi,

       

      I am trying to count securities (dimension) that are associated with 3 or less accounts (another dimension). Both IDs appear in my fact table. I need the amount per advisor ID, a third dimension, also in the fact table. The following query actually works but is really slow:

       

      sum(aggr({<[Snapshot Date] = {"$(=max([Snapshot Date]))"}>}if(count(distinct [Account ID]) <= 3, 1, 0),[Security ID], [Advisor ID]))

       

      I guess the issue is the double aggregation in my query. Any idea on how I could get the same result a little faster?

       

      Thanks!