topic Re: FirstSortedValue and Max functions in QlikView

FirstSortedValue and Max functions

rishimessi19 — Wed, 03 Feb 2016 18:51:02 GMT

I have a requirement to replicate a straight table as individual text boxes as shown in screenshot below. I am using Max and First Sorted Value expressions (Expressions pasted below). Somehow these functions only consider distinct values. In my example "68" is repeated twice but Max function only considers it once and first sorted value ignore completely. How do I resolve this?

Expressions I am using:

max(Aggr(XXX,Dimension),1) ---->1,2,3,....7

FirstSortedValue(Dimension,-(Aggr(XXX,Dimension)),1) ---->1,2,3,....7

Re: FirstSortedValue and Max functions

Gysbert_Wassenaar — Wed, 03 Feb 2016 19:19:32 GMT

You need to make the max value unique somehow. Perhaps this will do:

FirstSortedValue(Dimension,-(Aggr(XXX*1000+Ord(Left(Dimension,1)),Dimension)),1)

Re: FirstSortedValue and Max functions

swuehl — Wed, 03 Feb 2016 19:22:50 GMT

Try something like

FirstSortedValue(Dimension,-(Aggr(XXX+FieldIndex('Dimension',Dimension)/1000 ,Dimension)),1) ---->1,2,3,....7

FirstSortedValue(Aggr(XXX,Dimension),-(Aggr(XXX+FieldIndex('Dimension',Dimension)/1000 ,Dimension)),1)