Get a Max number for a pivot

[#1]

Hi,

I need to find the Max number on a pivot. This seemed simple enough to me, but struggling. I want to put the max number into a textbox.

I've attached an image of the pivot.

Can some help, a user could select diffenerent specialties months and years.

Cheers

Simon

[hic]

You cannot get a max from a pivot. But you can calculate the same number using an Aggr():

Max(Aggr(Sum(Something),Speciality,Year,Month))

HIC

[#3]

THanks for the quick response. However I'm more confused now.

Let go back to the begining.

I have 2 charts and I want to have the scales the same, so I'm trying to find the Max number to fix the scale of the axes.

I want to show a line graph month by month of the number of referrals each sub specilaity has based on the specilaty I select. Graph 1 will show 1 year and graph 2 will show the previous year, but I want to fix the scales on eacvh chart so they are the same.

I've taken what you have sent me and tweaked it, but it works out the max for all specialtys not just the one i've selected.

Any help would be much appreciated

Cheers

[hic]

Then you could do a RangeMax(Max( ...1...), Max( ...2...)) where the ...1... denote an Aggr() call equivalent to the first chart, and ...2... to the second chart. Hence

     RangeMax(Max( Aggr(Count({$<Year={2012}>} ID),Month ) ), Max( Aggr(Count({$<Year={2013}>} ID),Month )))

But that would be to complicate it. You want two identical charts with different years in the two charts. Then you should make a Trellis chart with Year as first dimension. See Chart Properties - Dimension - Trellis.

HIC

[#5]

Hi,

Thank you for your reponses, I feel i'm getting a bit closer. Like the idea of Trellis tables, not sure its exactly what I want.

I've tried the code which works great as long as no year is selected. As long as the year is selected that I'm specifying in the code, then it works.Which does net help me when trying to compare 2 years.

Any more thoughts?