14 Replies Latest reply: Dec 27, 2015 11:14 PM by kushal chawda

# How to make line chart able to display trend line appear when i click on month = 7 ?

Hi All

I have a trend line , I like to make it able to display the trend line , when i click on month = 7.

Paul

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Would that be the same trend or different one?

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

I hope you aware that you model is very complicated and have several synthetic keys.?

Feeling Qlikngry?

How To /Missing Manual(25 articles)

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Robert

Must be same trend , I mean trend line cannot change right.

Paul

Sent from my iPhone

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

You can ignore selection by using either 1 at the beginning of your expression or = sign for particular dimension

Example:

ignore all selections except for some selected columns

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Robert

Thank you for sharing with me John explaination on SET analysis which is 3 year back.

Paul

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi,

Check this,

Trend line?

Hope this helps,

Regards,

Hirish

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Paul,

I agree with Robert that your model is not very good at all.

Concerning your expression, I don't know why you use three time the same condition :

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, If(Mod(RowNo(),12) = 0, 12, Mod(RowNo(),12)) * 30)

*

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

RangeAvg(Aggr(Sum({<month>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year), sum({<month>}STK_CLOSE)))

/

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeSum(Above(Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

You could use only one time, it will be clearer and more effective :

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

If(Mod(RowNo(),12) = 0, 12, Mod(RowNo(),12)) * 30

*

RangeAvg(Aggr(Sum({<month>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year), sum({<month>}STK_CLOSE))

/

RangeSum(Above(Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo()))

)

And finaly, you will have your trend displayed when you select a month if you change the condition of your expression like that :

If(sum({<month=>} STK_CLOSE) > 0 and sum({<month=>} STK_OPEN) > 0,

If(Mod(RowNo(),12) = 0, 12, Mod(RowNo(),12)) * 30

*

RangeAvg(Aggr(Sum({<month=>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year), sum({<month=>}STK_CLOSE))

/

RangeSum(Above(Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo()))

)

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Seb

Thank you vey much for sharing with me about how to make the script more organised. But when i paste your expression into my chart , my chart line give wrong result.

Paul

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

You're right, I worked on the reason why the calculation was not calculated for every month. I used internal expressions without modification thinking they were correct.

Here the expression with the internal correction of Kush and my reorganization:

If(sum({<month=>}STK_CLOSE) > 0 and sum({<month=>}STK_OPEN) > 0,

If(Mod(RowNo(),12) = 0, 12, Mod(RowNo(),12)) * 30

*

RangeAvg(Sum({<month=>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), sum({<month>}STK_CLOSE))

/

RangeSum(Above(Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo()))

)

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

try this expression

If(sum({<month>}STK_CLOSE) > 0 and sum({<month>}STK_OPEN) > 0, If(Mod(RowNo(),12) = 0, 12, Mod(RowNo(),12)) * 30)

*

If(sum({<month>}STK_CLOSE) > 0 and sum({<month>}STK_OPEN) > 0,

RangeAvg(Sum({<month=>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), sum({<month>}STK_CLOSE)))

/

If(sum({<month>}STK_CLOSE) > 0 and sum({<month>}STK_OPEN) > 0, RangeSum(Above(Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Kush

Thank you so much your script is working fine. Now i am still able to view the trend line while viewing the Table report using SET expression for display same thing for compare CY vs LY diff.

You from which country ?

Paul

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Thanks Paul.

I am from India

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Hi Kush

Are you work in India also ?

Paul

Sent from my iPhone

• ###### Re: How to make line chart able to display trend line appear when i click on month = 7 ?

Yes, I work here in India.