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

[paulyeo11]

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

[robert_mika]

Would that be the same trend or different one?

[robert_mika]

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

Feeling Qlikngry?

How To /Missing Manual(25 articles)

[paulyeo11]

Hi Robert

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

Paul

Sent from my iPhone

[robert_mika]

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

[HirisH_V7]

Hi,

Check this,

Trend line?

Hope this helps,

Regards,

Hirish

[sfatoux72]

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

)

[Kushal_Chawda · Accepted solution]

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]

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]

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