Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Valued Contributor II

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

1 Solution

Accepted Solutions

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())))


14 Replies

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)

paulyeo11
Valued Contributor II

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

hirishv7
Honored Contributor

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

“Aspire to Inspire before we Expire!”
sfatoux72
Valued Contributor

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 ?

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())))


paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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