FirstSortedValue or Max() value

[avastani]

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.

[jagan]

Hi,

Can you attach some sample file.

Regards,

jagan.

[#3]

hi

see the attached file on firstsorted.

[er_mohit]

have you tried

aggr(max(value),_kMasterDate)

or

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

[jagan]

Hi,

PFA file .

Regards,

Jagan.

[avastani]

Thanks.

[avastani]

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

[avastani · Accepted solution]

what did work however is,

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

[avastani]

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