7 Replies Latest reply: Jul 9, 2015 4:28 PM by Sinan Ozdemir RSS

    Aggr and set analysis

    Kevin Case

      I have been trying to figure out a way to get the measure that I need for my pivot table without creating a new field in the load script.  I was hoping to accomplish this via the AGGR function and set analysis.

       

      What I'm trying to do is Sum(IDCount) where the earliest accounting date falls between a starting and ending date.  Consider the following sample data:

       

         

      IDDivisionBranchAccountingDateIDCount
      1126/1/20151
      1126/10/20151
      2124/1/20151
      2126/12/20151
      3126/15/20151
      3126/18/20151
      4136/2/20151
      5133/25/20151

       

       

      If my starting date is 6/1/2015 and my ending date is 6/30/2015, I expect three rows to match my criteria (highlighted in yellow).  I would like to use this measure in a pivot table that has the dimensions of Division and Branch.  I would like to display the following:

         

      DivisionBranchTotal
      122
      131

       

      Thinking in set analysis mode, I would like to do the following:

       

      Sum({1<Min([AccountingDate])={">=$(=Date($(v3)))<=$(=Date($(v4)))"}>}[IDCount])

       

      However, this is not possible.  I was thinking that if I could use AGGR, it may be possible.  Something like:

       

      Sum({1<Aggr(Min([AccountingDate]), [Division], [Branch], [ID])={">=$(=Date($(v3)))<=$(=Date($(v4)))"}>}[IDCount])

       

      Is this possible?  Anyone have any suggestions as to how this can be achieved?  Not sure if I can apply set analysis over the field that is being aggregated.

       

      Thanks.

       

      Kevin