Getting the value from last day

[#1]

hello all,

i have a little problem to get the result i want,

i have a table like the following:

ID	Type	TIME	VAL
3	A	1/1/11 1:00	100
3	A	2/1/11 1:00	200
3	A	3/1/11 1:00	300
4	A	1/1/11 1:00	400
4	A	2/1/11 1:00	600

i need to create a pivot table that present the sum of the values for Type A but taking only the last TIME value,

something like this:

Type	sum
A	900

i have tried set analysis but no luck, i have tried FirstSortedValue and no luck as well,

any one have an idea?

thank you all!

[Anonymous]

I think there might be an issue with the set analysis syntax you are using.  I was able to load the table you provided inline and come up with a working expression:

SUM({$<Time={'$(=MAXSTRING(Time))'}>} Val)

This might differ for you depending on how QlikView is viewing the Time field.  You might or might not have to use the quotes or you might need to use MAX rather than MAXSTRING.

Aaron

[#3]

thank you i will try!

[swuehl]

The last TIME value is '3/1/11 1:00', right? Shouldn't the sum of VAL be 300, then, instead of 900?

I assume you mean to sum the VAL at the last TIME value per Type and ID?

You could try this in a pivot with dimension Type:

=sum(aggr(if(TIME= maxstring(total<Type,ID> TIME),VAL),Type,ID,TIME))

or if your TIME is a recognized Timestamp type with numerical representation, e.g. by using this in the script:

SET TimestampFormat='D/M/YY h:mm';

you could use FirstSortedValue as your pivot expression:

=sum(aggr(FirstSortedValue(VAL, -TIME),Type,ID))