Indranil-Be careful using grouping & aggregation on Date/Time fields. When visualising and calculating with Dates/Time fields, it is often helpful to create duplicate fields like Month and Year which are typed as Text Categories, rather than Dates/Times. You can then easily use Subset functions to calculate derived values over grouped/aggregated time periods.

Given duplicate category fields formatted to display only the week number (w), month name (MMMM) and year (yyyy) of any given Data/Time value, the formula field [Max Revenue by weekly period] could be defined with the following formula:

SUBSET_MAX([Revenue],SUBSET3([Week],[Month],[Year],[Week],[Month],[Year]))

Broken down, this formula means:

SUBSET_MAX( [Revenue], says find the maximum value of the field [Revenue] for each unique combination of all values in these fields:

SUBSET3([Week],[Month],[Year],[Week],[Month],[Year] ) = all unique combinations of Week, Month, and Year in the data set

This will create 4 or more duplicated values per month per year, all the same, all equal to the maximum weekly revenue in that month.

Also be careful when grouping/rolling up in the Table View by say, Month..the default will SUM the individual duplicated values, which is not correct...you must set the Grouping function on the Subset formula field to Mean to see the correct value for a roll-up to monthly.

More information on Subset functions is here: http://www.visokio.com/kb/subset-functions

Thanks Tom. This is very helpful. I could achieve the desired effect.