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
      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.