Finding the max value

[#1]

Hi everyone,

I have a problem, i think it would be pretty simple to all of you experts - i need to find the max value between calculated rows.

I tried to use agrr and firstsortedvalue functions but nothing seems to work

I have a pivot table chart with 6 Dimensions (3 are calculated dimensions, marked in yellow) and a calculated expression(orange).

the expression value is affected by the margin and override dimensions column.

step 1: find the max value of the calculated expression, for every ID ,start, end and target

Step 2: find the margin linked to max value of the calculated expression between two Targets.

in the example below the needed value is 2% (which is the margin of the max between $700 and $1000)

ID	Start	End	Target	override	Margin	Calculated Expression	step1: AGRR needed value
1	1/1/2014	5/1/2014	100	10	1%	$500	$700
					2%	$600
					3%	$700
			200	20	1%	$800	$1,000
					2%	$1,000
					3%	$9,000

thank you very much!

[swuehl]

I believe the critical point here is the way you've defined the first three calculated dimensions.

We would need to know the definitions of all your dimensions and expressions in that chart, and probably something about your data model. The easiest way to provide all information would be to upload a small sample QVW that replicates your setting and your chart (without the need to upload your real data).

If it's possible to define your calculated dimensions as fields in your data model, I would suggest to do so, this will make things a lot easier.

[#3]

Hi,

thank you for the quick response! i will try to define the dimensions as fields in the script and will upload the sample. thanks again.