Maximum value from multiple years in KPI

[Venkey077]

Hi Eveyone,

I need to showcase maximum value across YRQTRs, pls find screenshot for reference.

YRQTR	Sales		Formula
Y	100
Y1Q1	200		(Y1Q1-Y)/Y	100	Maxm value =
Y1Q2	300		(Y1Q2-Y)/Y	50	100
Y1Q3	400		(Y1Q3-Y)/Y	33.33333333				Notes :
Y1Q4	500		(Y1Q4-Y)/Y	25				Y is Reference point of YRQTR
Y2Q1	600		(Y2Q1-Y)/Y	20
Y2Q2	700		(Y2Q2-Y)/Y	16.66666667
Y2Q3	800		(Y2Q3-Y)/Y	14.28571429
Y2Q4	900		(Y2Q4-Y)/Y	12.5
Y3Q1	1000		(Y3Q1-Y)/Y	11.11111111
Y3Q2	2000		(Y3Q2-Y)/Y	100
Y3Q3	3000		(Y3Q3-Y)/Y	50
Y3Q4	4000		(Y3Q4-Y)/Y	33.33333333
Y4Q1	5000		(Y4Q1-Y)/Y	25
Y4Q2	6000		(Y4Q2-Y)/Y	20
Y4Q3	7000		(Y4Q3-Y)/Y	16.66666667

Always denominator should be 'Y' value only, numerator will  be different quarters.

I need to display maximum value.

Please find attached screenshot for the reference.

Please help me on this task.

regards,

Venkey.

[maheshkuttappa]

If  Y value needs to be constant use -(Sum(Sales)-sum( Total {<YRQTR={'Y'}>}Sales))/sum( Total {<YRQTR={'Y'}>}Sales)

If value needs be based previous row use - (Sum(Sales)-above(sum(Sales)))/above(sum(Sales))

[Venkey077]

Thanks for your reply..

In your example, highest value is 59..

I want display that in KPI object, how to do that ?

[Gabbar]

Try this:
Max(aggr(max(Sales),YRQTR))

[maheshkuttappa]

This will be the right expression 

Max(Aggr((Sum(Sales)-sum( Total {<YRQTR={'Y'}>}Sales))/sum( Total {<YRQTR={'Y'}>}Sales),YRQTR))

Please mark the answer as correct and open a new thread for new questions. This will help others looking for similar solutions. Thanks