Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Reference line in bar chart

Hello,

Below is the bar chart where I showing wind distribution. 

I need to show reference line, first reference line is always at wind speed 3. Other reference line has to be dynamic which

always has to nearest first value greater than or equal to 2 %.  and corresponding to that wind speed. 

I am using below expression for the chart.num(Count({ $<Year={$(vMaxYear)}> }WIND_SPEED_DIST)/sum(total aggr(count(distinct { $<Year={$(vMaxYear)}> } POW_CURVE_DATE),WIND_SPEED_DIST)),'##.##%')

 

 

 

clipboard_image_0.png

2 Replies
Highlighted
Partner
Partner

Hello Sandeep,

 

Thank you for the brainteaser!

Few challenges I faced on the way:

1. It's good that your dimension is a numeric value (otherwise you need to use an indexation in the back-end prior to building your chart)

2. There are null values in the data set attached (make sure you use set expression to filter them out)

 

I used combo chart with the following expression for value of the bar:

Count(DATE)/Count(TOTAL DATE)

Make sure in the Axes section -> Dimension Axis pane -> Continuous is checked

Here is the solution (this expression as a Reference line -> Presentation -> Reference Lines) :

=
Len(
KeepChar(
SubField(
Concat(Aggr(ROUND(Count({<WIND_SPEED_DIST={'*'}>} DATE)/Count({<WIND_SPEED_DIST={'*'}>} TOTAL DATE), '0.00001'), WIND_SPEED_DIST), ';',WIND_SPEED_DIST)
,
ROUND(Min(
Aggr(
IF(Count({<WIND_SPEED_DIST={'*'}>} DATE)/Count({<WIND_SPEED_DIST={'*'}>} TOTAL DATE)-0.02>0, Count({<WIND_SPEED_DIST={'*'}>} DATE)/Count({<WIND_SPEED_DIST={'*'}>} TOTAL DATE))
,WIND_SPEED_DIST
)
), '0.00001')
)
, ';')
)
+Min(WIND_SPEED_DIST)

 

In general, we need to first calculate whichever is the value closest but still >= to 2%.

Then we need to artificially plot all values of the % distribution and find where in this sequence is our value to identify whichever the dimension value is. Then we count our indexation symbol occurrences to find which is the exact dimension value.

Results:

image.pngimage.pngimage.png

 

Let me know if this helps!

 

Regards,

S.T.

Digital Support
Digital Support

Sandeep, did Stoyan's post help you get what you needed?  If so, do not forget to come back and use the Accept as Solution button on his post to give him credit and let others know it worked.  If you are still working upon things, leave an update for us.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.