topic Getting the value from last day in QlikView

Getting the value from last day

Sun, 08 Jan 2012 18:03:04 GMT

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!

Getting the value from last day

Anonymous — Sun, 08 Jan 2012 18:55:36 GMT

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

Getting the value from last day

Sun, 08 Jan 2012 19:14:45 GMT

thank you i will try!

Getting the value from last day

swuehl — Mon, 09 Jan 2012 00:39:12 GMT

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))