FirstSortedValue in script returns different results than expression

[Anonymous]

,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!

[sunny_talwar]

When you used the above as an expression... what was your dimensions in the chart?

[Anonymous]

Division, Type, Name

[sunny_talwar · Accepted solution]

1) You can use this in your dimension like this.... (if you don't want to create this in the script

Aggr(

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]))),

Division, Type, Name)

2) When you used this in the script... did you use Group By Division, Type, Name or were there more dimensions in your chart?

[Anonymous]

This worked as a dimension! Thank you so much.