in Bar chart values on data points on overlapping

[Ramya]

Hi Guys,

i am new to qlikview . i had built one bar chart with 2 expressions and one dimension. i need to have the value on data points  for both the expressions. but if i select values on data points for both it is getting overlapped.

can anyone help to get rid of this.

here iam attaching the image

[Ramya]

Sorry it is combo chart not bar chart

[Ramya]

Hi ,

can anyone help me on this?  is there any alternative 

[Brett_Bleess]

The only option I see would be to do Text as Pop-up instead of Text on Data Points...

Regards,
Brett

[gavinlaird]

I would suggest unselecting "Values on Data Points" for your existing expression, then creating a separate expression that has all display options unselected except "Values on Data Points".

That expression will need to determine whether the data points for Planned and Actual are too close to each other (in the example below, they are determined to be too close if the difference is less than 2). If so, the expression will display both Planned and Actual values on one of the lines, separated by a full newline to avoid overlapping. If not, the expression displays the value directly on the line as usual.

if(fabs(only({<Type={'Planned'}>} TOTAL <Date> Value)-only({<Type={'Actual'}>} TOTAL <Date> Value))>2,
	Value,
	if(Type='Actual',
		Concat(TOTAL <Date> Value,chr(10),-Value)))

Replace the "Type" dimension with whatever dimension stores "planned" and "actual". Replace "Value" dimension with whatever dimension stores the number values at each date.

This will result in a chart looking like this. The qvw containing this chart is attached.

[Ramya]

Hi Gavinlaird,

Thank you for the response.

But here i am using only one dimension which is 'All_Milestone_Date' and i am using the 2 expressions which are

1.Planned Execution------ rangesum(above(Count(DISTINCT{<st-={'N/A'}, Actual_Flag={'N'}>}Test_ID_Count) , 0, Rowno()))

2.Actual Execution----------if(Date(All_Milestone_Date)>Today(),' ', rangesum(above(Count(DISTINCT {<st={'Passed','Failed'}, Actual_Flag={'Y'}>} Test_ID_Count), 0, Rowno())))

in the expression which you have provided what i need to pass in the type dimension as i am having only one dimension in my chart and also what should i pass in the value.

can you please help me with this.

[gavinlaird]

Oh i see! In that case, I would instead suggest creating three new expressions. The first two will be invisible lines that display the "Values on Data Points" on dates where the Planned/Actual data points are not so close that they overlap. The third new expression will display the values from both of your expressions on the dates where the data points are so close that they overlap.

First, un-check "Values on Data Points" for your two existing expressions. Then, create the following three expressions:

1) This expression displays the values for the Planned Execution expression (column 1). For "Display Options" check "Line" and "Values on Data Points".

if(fabs(Column(1)-Column(2))>2,
	Column(1))

 2) This expression displays the values for the Actual Execution expression (column 2). Select the same display options as above.

if(fabs(Column(1)-Column(2))>2,
	Column(2))

 3) This expression displays both the Planned Execution and Actual execution values. Select the same display options as above.

if(fabs(Column(1)-Column(2))>2,
	'',
	Dual(if(Column(1)>Column(2),
		Column(1)&chr(10)&Column(2),
		Column(2)&chr(10)&Column(1)
	),Column(1)))

Lastly, set the "Background Color" of each of those three new expressions to "argb(0,0,0,0)" to make them transparent. 

The attached qvw shows an example.

[Ramya]

Hi,

Thank you so much for the response and  your inputs.

i replaced the expressions with my expressions. But unfortunately it is overlapping again.

can you kindly help to figure out this one.

here i am attaching the qvw file.

[gavinlaird]

The main issue was that my logic to check if the labels were overlapping was to see if the difference between Planned Execution and Actual Execution was <= 2. However, your dataset had larger numbers, so a more reasonable threshold would be 20. In the attached file, that logic compares to the vOverlappingLabelsThreshold variable, which you can edit in the variable overview to adjust to the exact value you want. 

Also, I switched it back to using the Column() references. Feel free to do it either way, but if you copy the expression over instead of using the column reference, you will have to remember to edit that expression in both places anytime you make a change in the future. Also, using the column references makes the code a lot cleaner and easier to troubleshoot, or tinker with in the future.