topic Re: Exponential Forecast in QlikView

Exponential Forecast

tracycrown — Thu, 07 Jan 2016 00:01:01 GMT

Dear all

Can someone help to show me an example on how to write a script in expression for exponential foreast by months?.

YearMonth	Sales Volume	Exponential Forecast (0.1)	Exponential Forecast (0.7)
Oct-2014	90
Nov-2014	106
Dec-2014	152
Jan-2015	244
Feb-2015	302
Mar-2015	274
Apr-2015	162
May-2015	194
Jun-2015	312
Jul-2015	359
Aug-2015	215
Sep-2015	126
Oct-2015	94
Nov-2015	125
Dec-2015	147

Thank you very much

Tracy

Re: Exponential Forecast

sunny_talwar — Thu, 07 Jan 2016 00:56:28 GMT

What is the expected output? What does Exponential Forecast(0.1) translate into numbers?

Re: Exponential Forecast

PaulVanSiclen — Thu, 07 Jan 2016 01:05:30 GMT

Tracy,

It appears you are trying to build a matrix of values based on month, 'Sales Volume' and a variable. You may want to take a look at my "Finance Toolkit" (link below)

Finance Toolkit

I have several examples of building tables based on dimensions, expressions and variables that might be used as a guide. Specifically check out the loan amortization tab which is probably one of the easiest to understand.

Best,

PVS

Re: Exponential Forecast

settu_periasamy — Thu, 07 Jan 2016 03:36:18 GMT

Hi,

May be like this ?

LET vExp1=0.1;
LET vExp2=0.7;
T1:
Load MonthName(Date#(YearMonth,'MMM-YYYY'))as YearMonth,[Sales Volume];
LOAD * INLINE [
    YearMonth, Sales Volume
    Oct-2014, 90
    Nov-2014, 106
    Dec-2014, 152
    Jan-2015, 244
    Feb-2015, 302
    Mar-2015, 274
    Apr-2015, 162
    May-2015, 194
    Jun-2015, 312
    Jul-2015, 359
    Aug-2015, 215
    Sep-2015, 126
    Oct-2015, 94
    Nov-2015, 125
    Dec-2015, 147
];
NoConcatenate
T2:
LOAD YearMonth
  ,[Sales Volume]
  ,if(RowNo()=1,[Sales Volume],
  Num((1-$(vExp1))*[Sales Volume]+$(vExp1)*Peek([Exp_Forecast(0.1)],RowNo()-2),'#,##0')) as [Exp_Forecast(0.1)]
  ,if(RowNo()=1,[Sales Volume],
  Num((1-$(vExp2))*[Sales Volume]+$(vExp2)*Peek([Exp_Forecast(0.7)],RowNo()-2),'#,##0')) as [Exp_Forecast(0.7)]
Resident T1 Order by YearMonth;
DROP Table T1;

Re: Exponential Forecast

tracycrown — Mon, 11 Jan 2016 00:13:26 GMT

Dear Settu

Thanks for your quick response.

Kindly advise what is the logic or formula for following statement ?.

Num((1-$(vExp1))+$(vExp1)Peek(,RowNo()-2),'#,##0'))

Tracy

Re: Exponential Forecast

tracycrown — Mon, 11 Jan 2016 00:32:02 GMT

Dear Settu

The exponential result should be as follows, please help :

YearMonth

Sales Volume

Exponential Forecast (0.1)

Exponential Forecast (0.7)

Oct-2014

Nov-2014

106

Dec-2014

152

101

Jan-2015

244

137

Feb-2015

302

113

212

Mar-2015

274

132

275

Apr-2015

162

146

274

May-2015

194

148

196

Jun-2015

312

153

195

Jul-2015

359

169

277

Aug-2015

215

188

334

Sep-2015

126

191

251

Oct-2015

185

164

Nov-2015

125

176

115

Dec-2015

147

171

122

169

140

Thank You

Tracy

Re: Exponential Forecast

sunny_talwar — Mon, 11 Jan 2016 02:41:34 GMT

I guess would you be able to explain how did you get those outputs? I mean to ask what is the calculation behind those numbers?

Re: Exponential Forecast

settu_periasamy — Mon, 11 Jan 2016 02:46:19 GMT

Hi,

Num((1-$(vExp1))+$(vExp1)*Peek(,RowNo()-2),'#,##0'))

I just tried the Normal Excel expression into QV.. Like

=(1-Exp Value)*Current Month Sales+Previous Month Sales*Exp Value

Already i stored the Exp value into variable.

Check the excel attachment. May be if you give your expression, let us try to adapt in qv.

Re: Exponential Forecast

settu_periasamy — Mon, 11 Jan 2016 03:12:23 GMT

Hi,

I think i got your result. I have used the below expression..

=(1-Exp Value)*Current Month Sales+Previous Month Sales*Exp Value

if you just interchange the Current Month Sales and Previous Month Sales, you will get your desire result

like =(1-Exp Value)*Previous Month Sales+Current Month Sales*Exp Value

Just ensure which one is correct.

Check the sample attachment..

Re: Exponential Forecast

tracycrown — Mon, 11 Jan 2016 09:02:48 GMT

Dear Settu

The result should be as follows :

YearMonth	Sales Volume	Exp(0.1)	Exp(0.7)
Oct-2014	90	90	90
Nov-2014	106	90	90
Dec-2014	152	92	101
Jan-2015	244	98	136
Feb-2015	302	112	211
Mar-2015	274	131	274
Apr-2015	162	145	273
May-2015	194	147	195
Jun-2015	312	152	194
Jul-2015	259	167	275
Aug-2015	215	186	333
Sep-2015	126	189	249
Oct-2015	94	183	162
Nov-2015	125	174	113
Dec-2015	147	168	120
		166	137

Re: Exponential Forecast

tracycrown — Mon, 11 Jan 2016 09:10:44 GMT

Dear Sunny

Sorry, I do not know how to get the results, it was downloaded from SAP.

Tracy

Re: Exponential Forecast

settu_periasamy — Mon, 11 Jan 2016 10:13:49 GMT

Hi,

I don't understand, why the previous 2 rows coming under current. Anyhow, as i mentioned earlier, you can just use the previous() in script to get the 2 rows. And in in the expression use Peek().

Check the Attachment..

Re: Exponential Forecast

tracycrown — Tue, 09 Feb 2016 02:38:36 GMT

Dear Settu

Please advise how to show just the highest total (2831) of Exp_Forecat in the chart (ie. 0.7) ?.

By the way, I failed to use the following in scrip, please help.

rangeavg(above(sum([Sales Volume])),-1,2))

Thank you

Tracy

Re: Exponential Forecast

settu_periasamy — Tue, 09 Feb 2016 02:58:19 GMT

Hi,

where do you want to show the total (2831) in the chart? generally you can use the expression.

=sum([Exp_Forecast(0.7)])

By the way, I failed to use the following in scrip, please help.

rangeavg(above(sum([Sales Volume])),-1,2))

there is one extra parenthesis..(not sure about the requirement)

rangeavg(above(sum([Sales Volume])),-1,2)

Can you post your qvw, and expected result?

Re: Exponential Forecast

tracycrown — Wed, 10 Feb 2016 08:30:46 GMT

Dear Settu

Instead of showing both Exp 0.1 & Exp 0.7 on the report, just display the one with MaxTotal.

YearMonth Sales Volume MaxTotal

Thank You

Tracy

Re: Exponential Forecast

settu_periasamy — Wed, 10 Feb 2016 08:56:05 GMT

Like This? If so, you can just use sum(TOTAL [Exp_Forecast(0.7)])

see attached..

Re: Exponential Forecast

tracycrown — Wed, 10 Feb 2016 22:19:07 GMT

Dear Settu

I would like to get the max between 0.1 & 0.7 or more variables in script instead of specifying 0.7 in sum Total,

Thank you

Tracy

Re: Exponential Forecast

settu_periasamy — Thu, 11 Feb 2016 03:38:34 GMT

Hi Tracy,

May be create one variable and assign the expression like below..

Variable Name : vMax_Forecast

Value : =if(sum(TOTAL [Exp_Forecast(0.1)]) > sum(TOTAL [Exp_Forecast(0.7)]),sum(TOTAL [Exp_Forecast(0.1)]),sum(TOTAL [Exp_Forecast(0.7)]))

Re: Exponential Forecast

tracycrown — Thu, 11 Feb 2016 09:25:17 GMT

Dear Settu

I had inserted Max_Exp in the script but nothing shown on table, please help.

if(RecNo()=1,[Sales Volume],
Num((1-$(vExp2))*Peek([Exp_Forecast(0.7)],RecNo()-2)+$(vExp2)*[Sales Volume],'#,##0')) as [Exp_Forecast(0.7)],

if(sum(TOTAL [Exp_Forecast(0.1)] ) > sum(TOTAL [Exp_Forecast(0.7)]),sum(TOTAL [Exp_Forecast(0.1)]),sum(TOTAL [Exp_Forecast(0.7)])) as Max_Exp

Resident T1 Order by YearMonth;
DROP Table T1;

Thank You

Tracy

Re: Exponential Forecast

tracycrown — Mon, 15 Feb 2016 09:53:56 GMT

Dear Settu

Please help on my request dated Feb 11, 2016.

Thank You

Tracy