topic Re: Reference line in bar chart in QlikView

Reference line in bar chart

sandeep_b — Wed, 16 Oct 2019 12:46:20 GMT

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)),'##.##%')

Re: Reference line in bar chart

Stoyan_Terziev — Mon, 21 Oct 2019 13:37:38 GMT

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:

Let me know if this helps!

Regards,

S.T.

Re: Reference line in bar chart

Brett_Bleess — Wed, 23 Oct 2019 17:24:41 GMT

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