dynamic static max axes?

[#1]

Hi all,

I want to set the static max on my bar chart expressions axes so that if expression evaluates to >100, static max on graph will be 100, otherwise auto adjust axes (i.e don't apply max).

The reason being it is a trellis chart showing growth of products by month vs. previous year. Some products have very small sales so growth can be very high, sometimes +500%. As the scale on the trellis is the same for each graph, some products which have high consistent sales have much smaller growth but are much more important.

So, it's not really a static max but a dynamic static max or a max that is only applied when condition is met. Hopefully that makes sense...?

Does anyone have an idea as to how to achieve this?

Many thanks.

APS

[johnw · Accepted solution]

I think you're stuck with one or the other, but you could still accomplish what you want. For instance, let's say you have a line chart where each line is a customer, and you're summing sales for each month.

static max = rangemin(100,max(aggr(sum(Sales),Customer,Month)))

The rangemin(100,...) tells us to never go over 100. The rest tells it to calculate the maximum value that will display in our chart, and use that as the maximum for the axis. So it's a static max that never goes over 100, but within that range, the max value is dynamic.

Another alternative would be to use a logarithmic scale.

[#3]

Works perfectly.

Many thanks.