topic Re: FirstSortedValue or Max() value in QlikView

FirstSortedValue or Max() value

avastani — Wed, 23 Oct 2013 02:48:09 GMT

How do I return the values for a Field X for the dates in Max(_kMasterDate). I am able to fetch the max(_kMasterDate) for each Month Year but not the values for that date. I also tried FirstSortedValue(X, -_kMasterDate) to no luck

FieldX can be a granular data field at daily level.

Re: FirstSortedValue or Max() value

jagan — Wed, 23 Oct 2013 03:11:47 GMT

Hi,

Can you attach some sample file.

Regards,

jagan.

Re: FirstSortedValue or Max() value

Wed, 23 Oct 2013 04:26:41 GMT

see the attached file on firstsorted.

Re: FirstSortedValue or Max() value

er_mohit — Wed, 23 Oct 2013 05:21:02 GMT

have you tried

aggr(max(value),_kMasterDate)

FirstSortedValue(aggr(max(Value),[Month Year]),-aggr(_kMasterDate,[Month Year]))

Re: FirstSortedValue or Max() value

jagan — Wed, 23 Oct 2013 08:49:43 GMT

Hi,

PFA file .

Regards,

Jagan.

Re: FirstSortedValue or Max() value

avastani — Wed, 23 Oct 2013 11:53:26 GMT

Thanks.

Re: FirstSortedValue or Max() value

avastani — Wed, 23 Oct 2013 12:01:26 GMT

this is all great, and thanks for the reply, however, it needs to scale from weekly to monthly to quarterly to yearly grabbing the max value at each step of the way so the qvw's do not work.

as for Aggr(max(value), _kMasterDate) i wrapped a sum and that was incorrect and tried a Max() and that was partially correct because it fetches the max(value) of 300 if November2013 value is less than October 2013 value

Re: FirstSortedValue or Max() value

avastani — Wed, 23 Oct 2013 12:03:34 GMT

what did work however is,

FirstSortedValue(aggr(max(Value),_kMasterDate),-aggr(_kMasterDate,_kMasterDate))

Re: FirstSortedValue or Max() value

avastani — Thu, 24 Oct 2013 03:02:18 GMT

is it possible to extend this to return the SUM(Value) for the max period? and also support the Product and Customer hierarchy?