How to calculate the Maximum price on basis of current selection of a date to previous 52 weeks

[#1]

Hi,

I have a requirement as follows:

When I select a date, i want to calculate the maximum price from that selected date to previous 52 weeks .

Date               Price

12/12/2013     500

12/12/2014      200

11/11/2014      400

when I select the date as 11/11/2014 I should consider from 11/11/2014 to 52 weeks prior to that and fetch the maximum value.in this case I should get result as 500.

similarly when I select a diff date,it should change dynamically.

How do I accomplish this in qv?

[datanibbler]

Hi rekha,

you have to devise a way so that, based upon the user's selection of a date, the 52 weeks prior to that (o that.ne year?) are selected. Then the max() function should return the maximum value in that range.

That could be done via a sheet-level trigger that selects, based on the user_selection in the date_field, all the dates starting from one year prior to that.

HTH

Best regards,

DataNibbler

[#3]

Hi ,

How do I create a trigger for this?

[MK_QSL]

=Max({<Date=, Date = {"$(>=Date(AddMonths(Max(Date),-12)-1))<=$(=Max(Date))"}>}Price)