Maximum Value Seen So Far in the Data

[ychaitanya]

Hi Folks

Can anyone please let me know if you have come across of the situation of finding " Maximum Value Seen So far in the Dimension Values ".

EX;

Categ, Data

1,20

2,35

1,85

2,92

1,40

2,20

1,10

2,50

1,60

2,78

1,100

2,100

Expected Result:

Categ, Data,<<NEW Column to Find Max Value Seen So Far>>

1,20,20

1,85,85

1,40,85

1,10,85

1,60,85

1,100,100

2,35,35

2,92,92

2,20,92

2,50,92

2,78,92

2,100,100

I need to find Monthly, Quarterly and Yearly Rolling Maximum Values like this in Pivot Table .

Any Help or idea is highly appreciated.

Thanks

Chey

[swuehl]

Maybe like

INPUT:

LOAD *, Recno() as RecID INLINE [

Categ, Data

1,20

2,35

1,85

2,92

1,40

2,20

1,10

2,50

1,60

2,78

1,100

2,100

];

Result:

LOAD Categ, Data, RangeMax(If(Peek('Categ')=Categ,Peek('Max')),Data) as Max

Resident INPUT

Order by Categ, RecID;

DROP TABLE INPUT;

[ychaitanya]

I have an Daily Data available for many different categories and subcategories  from Source System

where i would like to calculate

Rolling Daily Maximum Seen So Far

Monthly Maximum Seen So Far ( Max in Current Month which has to be rolled across max from each month and perform roll up)

Quarterly Maximum Seen So Far( Max Value Rolled up in that Quarter and across Quarters)

These metrics have to be calculated at the UI Pivot table Level.

Any suggestions on the Expressions how we can implement ? ( I think of Above() function but that doesn't help if user wants to see Max's at Month View and Quarterly View )

[swuehl]

You can play around with RangeMax() and Above() functions, like

Categ	RecID	Only(Data)	RangeMax(Above(Data,0,Rowno()))
1	1	20	20
	3	85	85
	5	40	85
	7	10	85
	9	60	85
	11	100	100
2	2	35	35
	4	92	92
	6	20	92
	8	50	92
	10	78	92
	12	100	100