Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to show recent a 3 months regression line in a 12 months bar chart

How to set up a regression line or trend line show the recent 3 months in a 12 months bar chart?

In particular, below graph show me all possible 12 months, but I only want to take show 3 months trend line? What is the possible solution?

linest_m(total aggr(if(sum([Cash_Payments]),sum([Cash_Payments])),Month),Month)*              

+linest_b(total aggr(if(sum([Cash_Payments]),sum([Cash_Payments])),Month),Month)

I tried changing only({3}Month, but the regression won't show up anymore if it changes. Please help.

Capture1.GIF

16 Replies
Highlighted
Specialist III
Specialist III

Hi,

Let's see if the below script helps you:

linest_m(total aggr(if(sum({<Month={">=$(Date(AddMonths(Today(),-3), 'MMM'))<=$(Date(Today(), 'MMM'))"}>}[Cash_Payments]),sum({<Month={">=$(Date(AddMonths(Today(),-3), 'MMM'))<=$(Date(Today(), 'MMM'))"}>}[Cash_Payments])),Month),Month)*            

+linest_b(total aggr(if(sum({<Month={">=$(Date(AddMonths(Today(),-3), 'MMM'))<=$(Date(Today(), 'MMM'))"}>}[Cash_Payments]),sum({<Month={">=$(Date(AddMonths(Today(),-3), 'MMM'))<=$(Date(Today(), 'MMM'))"}>}[Cash_Payments])),Month),Month)

Basically, Date(Today(), 'MMM') pulls the today's date in months and Date(AddMonths(Today(),-3), 'MMM') looks back three months and pulls the month name:

Capture.PNG

Hope this helps.

Thanks

Highlighted
Contributor II
Contributor II

Hi Sinan,

When I entered the formula, it return a red line only.

Capture12.GIF

Highlighted
Specialist III
Specialist III

Hi,

Do you happen to have sample data so I can try different things?

Thanks

Highlighted
Contributor II
Contributor II

sinanozdemir‌. Hi Sinan. Please see attached sample data. One correction, is it possible to create a regression line based on most recent, available 3 month data? Thanks so much for your help.

Highlighted
Specialist III
Specialist III

Hi,

So I got R2 and Ax numbers right for the last 3 months, let me know if this is something that you are looking for

BTW, you need to use numerical values of the months.

Capture.PNG

In the bar chart, I created another expression just to show the last three months cash payments and turned on the trendlines to show linear.

Capture.PNG

I am also attaching the app and I hope this helps you.

Thanks

Highlighted
Contributor II
Contributor II

Yes. Thank you.

Highlighted
Contributor II
Contributor II

One last question. I entered the formula in the measurement, and what formula should I use 3 months trend line?

Highlighted
Specialist III
Specialist III

Hi,

Well, The way how I did was to create another expression which was Sum([Cash Receipts]) and unchecked the bar box. After that for that expression, I chose to show the linear trend line.

Look at my qvw to see how I did it.

Thanks

Highlighted
Specialist III
Specialist III

Ok. So I created a combo chart. Bars are showing the cash payments and the line is showing the linear trend for the last three months. I also put a straight table showing the formula and the trend lines in numeric values:

Capture.PNG

The formula for the trend line is:

Capture.PNG

I am attaching the app.

Hope this answers all of your questions and fits into your needs.

Thanks