Dynamic Axis Maximum

[#1]

I am trying to calculate a dynamic maximum for an axis on a stacked bar chart

What I want is to calculate is the total of Sales Value for cases that are ‘Open’, ‘Won’ and ‘Lost’ by SalesPerson for the current selection(s) and then use the Salesperson with the highest total  to set the axis max.

The dimension is SalesPerson

The value required is used for an expression:

sum(if(Status = 'Open' OR Status = 'Won' OR Status = 'Lost', [Sales Value]))

I need to use the above calculation  (or set analysis equivalent) to find the Sales person with the highest total. From my investigations I don’t think I can use max() , FirstSortedValue() and I am not sure if I can use aggr() .

Can anyone help?

Regards

Jason

[CELAMBARASAN]

Hi,

     Use the expression as below in the axis max.

     =Sum(TOTAL {<Status={'Open','Won','Lost'}>} [Sales Value])

     U can achieve this using set analysis.

Hope it helps

Celambarasan

[v_iyyappan]

Hi,

     In Chart propertities -> Axes ->Scale ->StaticMax ->Paste this expression

= Sum(TOTAL {<Status={'Open','Won','Lost'}>} [Sales Value])

Regards,

Iyyappan

[#4 · Accepted solution]

Hi Jason,

Barring syntax error...

Following should give you the answer

max(aggr( sum(if(Status = 'Open' OR Status = 'Won' OR Status = 'Lost', [Sales Value])),SalesPerson))

If you have having chart (using the above under Axes will do the trick)

Regards

[#5]

Kulbir,

I thought it might be resolved using aggr(), but I couldn't work it out!

Thanks very much for your help.

Jason