Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Two Custom Trendlines using Linest and Set Analysis

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