Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Riadak
Contributor
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.

Annotation 2020-03-10 155248.png

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)
1 Solution

Accepted Solutions
Riadak
Contributor
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:

Annotation 2020-03-12 112635.png

 

Thanks again for the input.

View solution in original post

2 Replies
tm_burgers
Creator III
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. 

 

 

 

 

 

Riadak
Contributor
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:

Annotation 2020-03-12 112635.png

 

Thanks again for the input.