FirstSortedValue Problem

[#1]

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

[nagaiank]

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

load *,If(Qty= -1,Peek(Total),Qty) as Total;

load * Inline [

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

];

[#3]

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,

[#4]

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

[anbu1984 · Accepted solution]

Check this