topic Re: FirstSortedValue in script returns different results than expression in QlikView

FirstSortedValue in script returns different results than expression

Anonymous — Tue, 13 Mar 2018 17:25:39 GMT

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

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

thanks!

Re: FirstSortedValue in script returns different results than expression

sunny_talwar — Tue, 13 Mar 2018 17:27:50 GMT

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

Re: FirstSortedValue in script returns different results than expression

Anonymous — Tue, 13 Mar 2018 17:29:16 GMT

Division, Type, Name

Re: FirstSortedValue in script returns different results than expression

sunny_talwar — Tue, 13 Mar 2018 17:31:36 GMT

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?

Re: FirstSortedValue in script returns different results than expression

Anonymous — Tue, 13 Mar 2018 17:40:56 GMT

This worked as a dimension! Thank you so much.