Combo Chart Sync Primary and Secondary Y-Axis to same x-Axis

[Riadak]

Dear Qlik Community,

I am trying to setup a Combo chart showing yearly sales development on a line and the YoY % Difference on the bar. Primary axis shows absolute Units Sold (Sum(UNITS_SOLD)), Secondary shows change in % (Sum(UNITS_SOLD) / above(Sum(UNITS_SOLD))-1).

The issue I have is, that both y-axis should cross the X-Axis at 0.

I understand that this can be achieved by adjusting the Min/Max range with an expression for both Axis but I am not sure how to achieve this exactly.

Appreciate your help.

[tm_burgers]

Change the Range on the Secondary Axis from Auto to Min/Max - and set the min to -6000 and the max to 6000; also you should set a min/max on your Primary axis as well; so that changed in the data don't affect it. Maybe a Min of -.5 and a max of .5 based on what I see in your chart. 

If you wanted the min/max to still be dynamic you could do a formula to determine the max's in your data set. 

ie .    Min =     -(ceil(max(sum[UNITS_SOLD]),1000)

         Max =     ceil(max(sum[UNITS_SOLD]),1000)

as long as the min and max are the same number but + & - your 0 point will be in the middle of the chart. 

[Riadak · Accepted solution]

Hi Tm,

thanks for your input. You pushed me in the right direction with "as long as the min and max are the same number but + & - your 0 point will be in the middle of the chart"

However, the formula provided would not work. Reason being that I have to use Aggr over Year. In the end my dynamic solution looks like this:

for Units Sold Axis:

Min: =-Ceil(Max(Aggr(Sum(UNITS_SOLD),[Date.autoCalendar.Year])),1000)

Max: =Ceil(Max(Aggr(Sum(UNITS_SOLD),[Date.autoCalendar.Year])),1000)

For Period Var %:

Min:

= - if(

Ceil( Max( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1) >
- Floor( Min( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1),
Ceil( Max( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1),
Floor( Min( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1)
)

Max:

=if(

Ceil( Max( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1) >
- Floor( Min( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1),
Ceil( Max( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1),
Floor( Min( Aggr(Sum(UNITS_SOLD)/above(Sum(UNITS_SOLD))-1,[Date.autoCalendar.Year]) ), 0.1)
)

I wish there was a more efficient way of doing this but it works for now:

Thanks again for the input.