# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

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

 Key Rule Day Qty Current Total ABC 1 1 20 20 ABC 1 2 25 25 ABC 1 3 20 20 ABC 1 4 35 35 ABC 1 5 40 40 ABC 1 6 -1 null (should be 40) ABC 1 7 -1 null (should be 40) ABC 1 8 -1 null (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.

Thanks

1 Solution

Accepted Solutions
Honored Contributor III

## Re: FirstSortedValue Problem

Check this

4 Replies
Valued Contributor III

## Re: FirstSortedValue Problem

The following script will give a clue to get the result.

Key,Rule,Day,Qty

ABC,1,1,20

ABC,1,2,25

ABC,1,3,20

ABC,1,4,35

ABC,1,5,40

ABC,1,6,-1

ABC,1,7,-1

ABC,1,8,-1

];

Not applicable

## Re: FirstSortedValue Problem

Brendan,

Qlikview can actually solve your problem.  Make a Straight Table Chart, put the Key, Rule, Day as Dimensions and make an expression on Qty and another with the following  IF (Qty > 0,Qty, MAX( ALL AGGR (Qty,Day))) , name it Current Total and voila.

I usually don't do things at the script level unless necessary for performance or any other reason, because QlikView is a very powerful engine with tools and functions that can solve many of our daily work.

Regards,

Not applicable

## Re: FirstSortedValue Problem

Mario,

Thanks for the suggestion.  What if, however, the latest Qty is not always the largest/max value?  How would you amend your expression so that it always picks the Qty for the max/largest date?

Thanks

Honored Contributor III

Check this