Two Custom Trendlines using Linest and Set Analysis

Tue, 20 Sep 2016 18:52:03 GMT

Hi all,

I have some similar problem to a post that has already been marked as ‘answered’ called ‘Linest & Set Analysis’. Problem there is that Date is formatted as YYYYMM, hence the Trend line show a sharp bend between December and January which I want to avoid.

I would like to generate two different custom trendlines (based on linest m & linest b functions) separated by a date (e.g. Go Live Date). The first Trend line should be based on the values before, the second one on the values after the Go Live Date (represented by a variable).

Both trend lines should be calculated according to the current selection. E.G. if two month after the Go live are selected which have the same monthly value only the After Go Live Trend Line should be shown. It should than be a horizontal line at the height of the two monthly values selected.

The example file includes two different scenario:

Scenario 1: The Date is stored in YYYYMM format. This is very close to the post mentioned above.

Scenario 2: The date is stored in regular Excel file Format (number starting from 01.01.1900 being 1). Actually this is the preferred scenario since it allows to set the Go live Date visible as a vertical reference line.

My difficulties are in the correct use of the variable prpGoLiveMonth (respective prpGoLiveDate), the different date format as well as in the correct set analysis expression of the selections boundaries. So any hint about these three topics would be appreciated.

I hope you can help me - I have been playing trial an error for quite some time

Thank you

Benedikt

topic Two Custom Trendlines using Linest and Set Analysis in QlikView

Two Custom Trendlines using Linest and Set Analysis