Combo Chart with 2 Axes - Align Axes

[danielle_v]

Hi All,

I have a combo chart with 2 axes - as per below;

The axis on the left is for the utilisation line, and has been set so that it is static and will always show 0% - 100%, stepping up in 10% increments.

I would like the axis on the right (count of containers) to line up with this so that it looks neater - i.e. so that there are always ten increments. However, this axis cannot be static as the number of container may rise/decrease dramatically. So what I think I'm looking to do is write an expression to set the max as the maximum number of containers, rounded up to the nearest 10. I would then set the steps so that they are the max figure divided by 10, to give the 10 increments in line with the other axis.

For example, in the above version of the chart, the maximum number of containers shown is 104 (Jan-14). So, I'd like the scale for the axis to go from 0 - 110, going up in increments of 11. However, if then a different year was selected and the max no. of containers shipped in any month of that year was only 51, I'd like the scale for the axis to adjust to go from 0 - 60, going up in increments of 10.

Does anyone know of a way of doing this please? I've tried putting various expressions in the Static Max box, but not getting anywhere. The expression for Containers is as follows;

count({$<Cal_Year = {$vCurrentYear}, Cal_Month = >} distinct(Container))

Any help greatly appreciated!

Danielle

[swuehl · Accepted solution]

You don't want the total count, but the max count with regard to the periods you show as dimension, right?

Try an advanced aggregation, something like:

=max({$<Cal_Year = {$vCurrentYear}, Cal_Month = >}

aggr(

     count({$<Cal_Year = {$vCurrentYear}, Cal_Month = >} distinct(Container))

, YourDimensionFieldLikeMonthYear)

    )

[danielle_v]

Thank you so much, it worked perfectly!

I just added CEIL around your expression to round up to the nearest ten.

🙂