Scaling dynamic axes on a chart

[#1]

Hello,

I have a chart analyzing revenue over time for a company with the ability to drill down into individual office locations. Obviously, the revenue is much lower when looking at a individual office vs the entire company. I want to be able to zoom in on the revenue for the company (ex: have a scale of $10 mil up to $11 mil) then, when a office is selected, i want the chart to have a scale of something like $1mil up to $2 mil.

The settings i currently have are set to forced 0, and dynamic. It works, although it is zoomed out on the chart. I dont want it forced to 0, but i also cant have it be static because then when i drill down the axes are off.

any ideas?

[#2 · Accepted solution]

Hi

It think you can try the static min and static max fields using expressions that take the min/max value of an aggregation.

For example:

Min ( Aggr ( Sum ( valuefield ) , DateTimeField ))

Max ( Aggr ( Sum ( valuefield ) , DateTimeField ))

Although it suggests that these are static, when you use an expression then the scaling should change according to what you are selecting, the aggr function should aggregate over a dimension (which shuold be the same dimension as in your chart) and therefore, taking the min/max values should give you what you want. You can of course extend the functionality by rounding these values up/down to the nearest $1M.

Good luck,

[#3]

That works,

Thanks

[Anonymous]

Nigel,

this solution is Awesome! I'm still finding that there are peaks in my charts that sometimes poke just above the MAX Y-Axis but apart from that its working much better than anything I've had before.

I get around my problem by adding a nominal amount (or subtracting depending if its a MIN or MAX). ie

Max ( Aggr ( Sum ( valuefield ) , DateTimeField )) + 500

Thank you all the same.

John.

[#5]

Great solution, works wonders