Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Create a forecast based on seasonal curve and historical data

Hi, I am tracking data that is seasonal with some fluctuations.  I'd like to have my seasonal model as a table in Qlik Sense and update actual as they come in.  As new actuals are added, would like to automatically update the forecast based on the predefined curve.

qlikq4.PNG

Blue line, right axis is the seasonal curve.  Orange/left is actual data.  Integral under the blue curve is 1 (100% of the seasons volume).  What I want is to sum the last four data points of actuals, divide by the sum of the corresponding four points of curve, and use that factor to multiply the curve going forward, creating an estimate line:

qlikq5.PNG

Any ideas on how to approach this?

1 Solution

Accepted Solutions

Re: Create a forecast based on seasonal curve and historical data

May be like this

If(Sum(Actual) <> 0, Sum(Actual),

Sum(Curve)*

Sum(TOTAL {<WE = {"$(='>=' & Date(Max({<Actual = {'*'}>}WE, 4)) & '<=' & Date(Max({<Actual = {'*'}>}WE)))"}>}Actual)/Sum(TOTAL {<WE = {"$(='>=' & Date(Max({<Actual = {'*'}>}WE, 4)) & '<=' & Date(Max({<Actual = {'*'}>}WE)))"}>}Curve))


Capture.PNG

11 Replies

Re: Create a forecast based on seasonal curve and historical data

I think it should be possible, but it might help to test it out to give you the exact solution. Would you be able to share a sample to look at this?

Not applicable

Re: Create a forecast based on seasonal curve and historical data

WECurveActual
1/1/20170.00956387933
1/8/20170.01366268561
1/15/20170.013662613005
1/22/20170.013662611173
1/29/20170.013662612566
2/5/20170.013662611942
2/12/20170.013662613304
2/19/20170.013662614042
2/26/20170.013662613126
3/5/20170.015322713995
3/12/20170.016982815050
3/19/20170.018642916228
3/26/20170.02030317637
4/2/20170.02196316836
4/9/20170.023623118015
4/16/20170.023623118909
4/23/20170.023623118914
4/30/20170.023623118252
5/7/20170.023623119261
5/14/20170.023623118360
5/21/20170.023623118505
5/28/20170.023623117779
6/4/20170.021355316092
6/11/20170.0236231
6/18/20170.0236231
6/25/20170.0236231
7/2/20170.0236231
7/9/20170.0188985
7/16/20170.0219303
7/23/20170.0219303
7/30/20170.0219303
8/6/20170.0219303
8/13/20170.0219303
8/20/20170.0219303
8/27/20170.0219303
9/3/20170.0219303
9/10/20170.019825
9/17/20170.0214776
9/24/20170.0214776
10/1/20170.0214776
10/8/20170.0214776
10/15/20170.0204341
10/22/20170.0193905
10/29/20170.018347
11/5/20170.0173034
11/12/20170.0162599
11/19/20170.0152163
11/26/20170.0094485
12/3/20170.0141728
12/10/20170.0141728
12/17/20170.0141728
12/24/20170.0141728
12/31/20170.0099209

Re: Create a forecast based on seasonal curve and historical data

Might help to know what the expected output is in numerical terms based on the sample provided.... Also, for dimension are you using MonthStart or are you using continuous axis?

Not applicable

Re: Create a forecast based on seasonal curve and historical data

Edit - Attached excel file.

Estimate column attached.  Continuous axis.  Dates are week ending, cannot generate a real monthly chart from this data.

There's a couple of ways of creating the estimate line that don't produce exactly the same results - so matching this perfectly isn't necessary, I'm just looking for a framework to get started, if that makes sense.

WECurveActualEstimate
1/1/20170.00956382679337933
1/8/20170.01366260885618561
1/15/20170.0136626081300513005
1/22/20170.0136626081117311173
1/29/20170.0136626081256612566
2/5/20170.0136626081194211942
2/12/20170.0136626081330413304
2/19/20170.0136626081404214042
2/26/20170.0136626081312613126
3/5/20170.0153226951399513995
3/12/20170.0169827811505015050
3/19/20170.0186428671622816228
3/26/20170.0203029531763717637
4/2/20170.021963041683616836
4/9/20170.0236231261801518015
4/16/20170.0236231261890918909
4/23/20170.0236231261891418914
4/30/20170.0236231261825218252
5/7/20170.0236231261926119261
5/14/20170.0236231261836018360
5/21/20170.0236231261850518505
5/28/20170.0236231261777917779
6/4/20170.0213553061609216092
6/11/20170.023623126018118.85246
6/18/20170.023623126018118.85246
6/25/20170.023623126018118.85246
7/2/20170.023623126018118.85246
7/9/20170.018898501014495.08197
7/16/20170.021930299016820.46011
7/23/20170.021930299016820.46011
7/30/20170.021930299016820.46011
8/6/20170.021930299016820.46011
8/13/20170.021930299016820.46011
8/20/20170.021930299016820.46011
8/27/20170.021930299016820.46011
9/3/20170.021930299016820.46011
9/10/20170.01982499015205.69594
9/17/20170.021477636016473.26964
9/24/20170.021477636016473.26964
10/1/20170.021477636016473.26964
10/8/20170.021477636016473.26964
10/15/20170.020434084015672.86845
10/22/20170.019390531014872.46727
10/29/20170.018346978014072.06608
11/5/20170.017303425013271.6649
11/12/20170.016259873012471.26371
11/19/20170.01521632011670.86253
11/26/20170.00944851107246.974227
12/3/20170.014172767010870.46134
12/10/20170.014172767010870.46134
12/17/20170.014172767010870.46134
12/24/20170.014172767010870.46134
12/31/20170.00992093707609.322938

Re: Create a forecast based on seasonal curve and historical data

May be like this

If(Sum(Actual) <> 0, Sum(Actual),

Sum(Curve)*

Sum(TOTAL {<WE = {"$(='>=' & Date(Max({<Actual = {'*'}>}WE, 4)) & '<=' & Date(Max({<Actual = {'*'}>}WE)))"}>}Actual)/Sum(TOTAL {<WE = {"$(='>=' & Date(Max({<Actual = {'*'}>}WE, 4)) & '<=' & Date(Max({<Actual = {'*'}>}WE)))"}>}Curve))


Capture.PNG

Not applicable

Re: Create a forecast based on seasonal curve and historical data

Thank you!  I didn't know you could do a sum outside the dimension.  After seeing your solution, I found this link that explains it well: https://community.qlikview.com/thread/60299

I don't quite understand the statement inside the Total keyword.  I get that it's limiting the range by the dates, but it's not obvious to me how to build a similar statement on my own.  Do you have links that can help me self learn this?

I figured out a bunch of it, but specifically what I don't understand is this syntax:

{"$(=' conditionals ')"}

Re: Create a forecast based on seasonal curve and historical data

Since you only want the last four weeks data for the estimate purposes, I had to use set analysis to get the sum of actual for those periods. If you add the below to a KPI Object... you will see a range which would include the last 4 weeks of actuals and curve

='>=' & Date(Max({<Actual = {'*'}>}WE, 4)) & '<=' & Date(Max({<Actual = {'*'}>}WE))


Capture.PNG

Re: Create a forecast based on seasonal curve and historical data

Also, TOTAL has nothing to do the set analysis here... they are two separate things

Not applicable

Re: Create a forecast based on seasonal curve and historical data

I rewrote your formula to help me understand it, and it gave identical results:

Sum(TOTAL {<WE = {"$(='>=5/14/2017<=6/4/2017')"}>}Actual)

I understand how you used the date and max functions to create the conditional string.  What I don't understand is how the syntax in general works, what does $ mean, etc.