Hide trendline

[#1]

Hi there,

This is my first post, so hopefully someone can help me.

My client wants a trendline which if certain conditions are met (below a certain threshold), to hide it.

So then there would be the possibility that you see a trendline, nothing, and then the trendline again.

I tried to set a condition on the trendline, but it's not working. No problem with a dotted line for values below a certain threshold, but I can't make it disappear.

The dotted formula: If(Count(Distinct theshold_value) < 100,'<W0><S3>')

I also tried to use the HSL, but it's not working, to make the line transparent, but it's not working..

If someone knows the/a solution, please let me know.

Best regards,

Arjan

[pljsoftware]

Hi

you must to use a macro that show your trend line when you press a button for example.

'Get chart object
set graph1 = ActiveDocument.GetSheetObject(chartID) 'charID can be for i.g. "CH100"

graph.AddTrend 0,1,1 'Lineare
graph.RemoveTrend 0,1,1 'Lineare

set graphProp = graph.GetProperties
graph.SetProperties graphProp

Regards
Luca

[#3]

Thanks Luca, for the very quick response...

It's not quite what I mean.. I guess I didn't explain it well enough..

i.e.

A trendline for the last 12 months. But when a value is lower then 100 i.e. for the month of May I don't want to show the trendline for that specific month. So then you you the line from Jan-Apr and from Jun-Dec. I know I can change the properties of the line (thickness, type), but I can't seem to be able to amke it disappear for a month, or make the line the same color as the background for a specific month.

[#4 · Accepted solution]

Finally I found a way to hide the trendline based on condiftions.

I can make the trendline fully transparent with the following condition as background color:

if(sum(Value)<=below(sum(Value)),ARGB(0,0,0,0),lightgreen())

The first 0 in ARGB makes sure it's fully transparent, so I can still use any background color i want.

Hopefully someone can use this..

[#5]

Are you using a line graph with the trendline setting and setting the backgroung color for the expression?

That is what I am doing in one case and I can't make the trendline disappear conditionally or for that matter, even if I set the background color to =ARGB(0,0,0,0).

Am I missing something?

[#6]

Hi Luca,

I found your answer helpful to my case.

One thing I wonder - how do you toggle between the trendline show/hide in the macro?

Is this done with a variable?

Thanks!