4 Replies Latest reply: Dec 29, 2014 8:05 AM by anbu cheliyan RSS

    FirstSortedValue Problem

      I have a data set similar to the below table.  There are 3 dimensions in my table: Key, Rule, and Day.  Qty is the field/expression that represents the quantity for each given Key, Rule, and Day.  The Current Total field should represent another expression... if Qty <> -1 then display Qty, else display the last value where Qty <> -1 (and by 'last' I specifically mean the maximum day).

       

      When I use the following expression in a simple text box I get the correct result (40) that should display on all rows when Qty = -1: =FirstSortedValue({$<Qty = {"*"}-{"-1"}>}Qty,-Day)

       

      However, when I use that exact same formula in my expression in the table for the Current Total field, it returns null values instead of 40:

                                                                                               

      KeyRuleDayQtyCurrent Total
      ABC112020
      ABC122525
      ABC132020
      ABC143535
      ABC154040
      ABC16-1null (should be 40)
      ABC17-1null (should be 40)
      ABC18-1null (should be 40)

       

      I am going to guess this has to do with aggregating data in a specific manner.  Any help would be greatly appreciated.

       

      Thanks