9 Replies Latest reply: Mar 29, 2013 2:34 AM by Nizam HM

# Determining Min and Max values within a date range in a chart

I have a table with prices over a long period of time. I have successfully created a way to select a range of prices from the full set to get an average price across that date range selected using set analysis(this is done in a chart using an input box to select number of days for the date range).

What i cannot do is get the min and max prices within that range in the chart....is there anyone who can help please?

• ###### Re: Determining Min and Max values within a date range in a chart

Hi,

You can use the same expression which you have used for avg, just change avg to min or max.

If this doesnt work kindly upload the qvw file.

Regards,

Kaushik Solanki

• ###### Re: Determining Min and Max values within a date range in a chart

Hi Kaushik , the model is quite complex so this may help:

=Sum({\$<Cnt={">=\$(vDiff) <=\$(vCnt)"}>} CLOSE/\$(vDays)

That is the set im using to calculate the average price

The table has date and price in it. Prices are not always populated for a specific date so i created a sequenced number for the dates descending.

Cnt is a sequence number in the calendar

I use an outlier table for the "select date" and calculate backwards the number of days i want in the range to ge the average price for that selection based on the value i enter in the input box, e.g. select 2013/03/25, input box is 10(days) so i select 10 days worth of prices back from that to get the total and consequently an average.

Does that help?

Many Thanks

Gary

• ###### Re: Determining Min and Max values within a date range in a chart

Hi Gary,

You can use the same expression for MIN and MAX. something like shown below.

Min({\$<Cnt={">=\$(vDiff) <=\$(vCnt)"}>} CLOSE)

Max({\$<Cnt={">=\$(vDiff) <=\$(vCnt)"}>} CLOSE)

Regards,

Kaushik Solanki

• ###### Re: Determining Min and Max values within a date range in a chart

Thank Kaushik!

• ###### Re: Determining Min and Max values within a date range in a chart

Hi Kaushik , the model is quite complex so this may help:

=Sum({\$<Cnt={">=\$(vDiff) <=\$(vCnt)"}>} CLOSE/\$(vDays)

That is the set im using to calculate the average price

The table has date and price in it. Prices are not always populated for a specific date so i created a sequenced number for the dates descending.

Cnt is a sequence number in the calendar

I use an outlier table for the "select date" and calculate backwards the number of days i want in the range to ge the average price for that selection based on the value i enter in the input box, e.g. select 2013/03/25, input box is 10(days) so i select 10 days worth of prices back from that to get the total and consequently an average.

Does that help?

Many Thanks

Gary

• ###### Re: Determining Min and Max values within a date range in a chart

Hi Kaushik , the model is quite complex so this may help:

Sum({\$<Cnt={">=\$(vDiff) <=\$(vCnt)"}>} CLOSE/\$(vDays)

That is the set im using to calculate the average price

The table has date and price in it. Prices are not always populated for a specific date so i created a sequenced number for the dates descending.

Cnt is a sequence number in the calendar

I use an outlier table for the "select date" and calculate backwards the number of days i want in the range to ge the average price for that selection based on the value i enter in the input box, e.g. select 2013/03/25, input box is 10(days) so i select 10 days worth of prices back from that to get the total and consequently an average.

Does that help?

Many Thanks

Gary

• ###### Re: Determining Min and Max values within a date range in a chart

0-10

11-20

21-20

do u want to find the min and max price in between the range is that u r asing or what