4 Replies Latest reply: Mar 13, 2018 1:41 PM by Ashley Navin RSS

    FirstSortedValue in script returns different results than expression

    Ashley Navin

      ,if([Covenant Days Past Due] > 0, 'Past Due', if([Covenant Days Past Due] < -180, 'Future >180 Days Away', if([Covenant Days Past Due] >= -180 and [Covenant Days Past Due] <= 0, 'Pending Next 6 Months',

        firstsortedvalue([Covenant Compliance Status],-[Covenant Compliance Last Modified Date])))) as [Covenant Compliance Complete Status]

       

       

      I am using the above calculation in an expression and it works great. I now want to use it as a "bucket" so I tried to move it to a dimension. I read you cannot use first sorted value in a dimension so I tried to move the below section to the script to store and then be able to use in dimension. This gave me way different results in the chart then it does when I use the whole thing as an expression.

      firstsortedvalue([Covenant Compliance Status],-[Covenant Compliance Last Modified Date]) as [Covenant Compliance Complete Status]


      New code in chart that displays incorrect results:

      ,if([Covenant Days Past Due] > 0, 'Past Due', if([Covenant Days Past Due] < -180, 'Future >180 Days Away', if([Covenant Days Past Due] >= -180 and [Covenant Days Past Due] <= 0, 'Pending Next 6 Months', [Covenant Compliance Complete Status])))

       

      Any idea why this doesnt work? I really need this as a dimension.

       

      thanks!