Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linest & Set Analysis

Hi all,

I have a problem with generating a custom trendline (based on linest m & linest b functions) and combining it with a set analysis.

I have 12 monthly figures representing sales, through Sept 2012 - Aug 2013. Here a prerequisite is that when I choose one month the previous 11 months + the currently selected month is visible in the chart. For this I inject a set analysis like:

( { <Year =, Month ={">=$(='201209') <=$(=max(Month))"}>} Sales)                              (nevermind the hardcoding of the monthnum)

So now I want to create a trendline which only plots the trend for the sales in 2013. I figured I inject the same set expression into the linest expression as follows:

( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>} total aggr(sum( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>}  Sales),Month),Month)*Month
+
linest_b( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>} total aggr(sum( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>}  Sales),Month),Month)

However this only returns a dot.

I have experimented without the set analysis, in fact selecting the entire period. And it Works fine with the manual linest trendline, however it is a requirement that the set expression is included, due to the fact that I want to choose only one month and get the rolling 12M figures displayed in the chart.

Please see my attached example, for further understanding. In the file a "QV generated" trendline is plotted for the Whole series (blue line). And the manual trendline that I am trying to create is visible as a red dot.

Any ideas on how to solve this problem??

Thank you,

David

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

linest_m( { <Year =, Month ={">=201301<=$(=max(Month))"}>} total aggr( sum( { <Year =, Month ={">=201301<=$(=max(Month))"}>}  Sales),Month),Month )

*only({ <Year =, Month ={">=201301<=$(=max(Month))"}>}Month)

+linest_b( { <Year =, Month ={">=201301<=$(=max(Month))"}>} total aggr(sum( { <Year =, Month ={">=201301<=$(=max(Month))"}>}  Sales),Month),Month )

View solution in original post

5 Replies
Nicole-Smith

linest_m(total aggr(sum( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>}  Sales),Month),Month)*Month

+linest_b(total aggr(sum( { <Year =, Month ={">=$(='201301') <=$(=max(Month))"}>}  Sales),Month),Month)

swuehl
MVP
MVP

Try

linest_m( { <Year =, Month ={">=201301<=$(=max(Month))"}>} total aggr( sum( { <Year =, Month ={">=201301<=$(=max(Month))"}>}  Sales),Month),Month )

*only({ <Year =, Month ={">=201301<=$(=max(Month))"}>}Month)

+linest_b( { <Year =, Month ={">=201301<=$(=max(Month))"}>} total aggr(sum( { <Year =, Month ={">=201301<=$(=max(Month))"}>}  Sales),Month),Month )

View solution in original post

Not applicable
Author

Thanks swuel!

Indeed this is what I was looking for!

Not applicable
Author

Thanks Nicole for your input

Not applicable
Author

Thx, could u plz explain whether u always need to use Total aggr or is there a situation where only aggr is relevant, according the reference manual it should always be there. What difference would it make?

I have the same question for Only(), when to include it and when should u leave it out?