Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Search instead for
Did you mean:
Contributor

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

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.

Labels (2)

• ### combochart

1 Solution

Accepted Solutions
Contributor
Author

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.

2 Replies
Creator III

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.

Contributor
Author

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.

Tags
Community Browser