Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())))
Would that be the same trend or different one?
I hope you aware that you model is very complicated and have several synthetic keys.?
Feeling Qlikngry?
Hi Robert
Must be same trend , I mean trend line cannot change right.
Paul
Sent from my iPhone
You can ignore selection by using either 1 at the beginning of your expression or = sign for particular dimension
Example:
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()))
)
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())))
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
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