3 Replies Latest reply: Sep 12, 2013 8:14 PM by Celambarasan Adhimulam RSS

    Evaluating one month, summing on YTD

    Steve Zagzebski

      I have the following type data:

       

       

       

       

      ^DeptCustDeptCustomerCustomerPctYearMonthCommission
      A|1A140%2012Jan100
      A|4A450%2012Jan150
      A|3A355%2012Jan200
      A|2A260%2012Jan250
      A|5A5100%2012Jan300
      A|1A140%2012Feb400
      A|2A260%2012Feb450
      A|2A260%2012Feb500
      A|3A365%2012Feb550
      A|4A4100%2012Feb600

       

      I need an object where a year and month are selected and  a variable for the CustomerPct. So (use data above) if a user selects Feb and 2012 and a CustomerPct of 65%, then the object returns all data from February that has a % of 65% or less. This expression works fine:

      sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)

      and  would return all blue lines from Feb 2012.

       

      However I need it to take all months = or less than the month selected FOR the combinations that are valid for February (A|1,A|2, and A|3).

      The result would look like:

      Dept          Commission     Distinct Customer Count

      A               2450                              3

       

      Looking for Commission to be YTD and how to get a distinct customer count.