topic Re: Forcasting in Qlikview in QlikView

Forcasting in Qlikview

Wed, 27 Sep 2017 23:29:21 GMT

Hi, I am new to Qlikview and have a requirement in relation to forecast and need your input/help.

The requirement is, for a Part, if the stock depletes by 20% a year from the current stock level, need to represent this using chart(graph) by month for the next 10 years.

Was able to come up with a formula to calculate this in excel, but not sure how to represent this in Qlikview.

Example Input:

Part, Stock, Rate of depletion per Year

PART A, 2000, 20%

PART B, 1000, 10%

PART C, 3000, 5%

In the above example for PART A, the stock would be

Year 1 - 2000

Year 2 -1600 (2000- 400)

Year 3 - 1280 (1600 - 320) and so on...

Appreciate your input.

Thanks

Venkat

Re: Forcasting in Qlikview

effinty2112 — Thu, 28 Sep 2017 08:15:04 GMT

Hi Venkat,

Try:

Data:

LOAD * INLINE [

Part, Stock, Rate of depletion per Year

PART A, 2000, 20%

PART B, 1000, 10%

PART C, 3000, 5%

];

OverTime:

LOAD

Part,

IterNo() as Year,

Stock*(pow(1-[Rate of depletion per Year],(IterNo()-1))) as Inventory

Resident Data

While IterNo()<=10;

Kind regards

Andrew

Re: Forcasting in Qlikview

effinty2112 — Thu, 28 Sep 2017 09:32:42 GMT

Hi Venkat,

If you would prefer to do this in the UI then use a calculated dimension

=ValueLoop(1,10)

labelled Year in the table below

with the expression

Stock*(pow(1-[Rate of depletion per Year],(ValueLoop(1,10)-1)))

To get

Year	Part	PART A	PART B	PART C
1		2000	1000	3000
2		1600	900	2850
3		1280	810	2707.5
4		1024	729	2572.125
5		819.2	656.1	2443.51875
6		655.36	590.49	2321.3428125
7		524.288	531.441	2205.275671875
8		419.4304	478.2969	2095.0118882812
9		335.54432	430.46721	1990.2612938672
10		268.435456	387.420489	1890.7482291738

Regards

Andrew

Re: Forcasting in Qlikview

Thu, 28 Sep 2017 12:15:11 GMT

Thanks Andrew, The solution you have proposed worked.Appreciate your help.

If I need to calculate stock depletion by month, if the rate of depletion is 20% per year, What would be the formula, I need to use? Thanks

Re: Forcasting in Qlikview

Thu, 28 Sep 2017 12:42:06 GMT

Was able to get it Andrew. Thanks

OverTime:

LOAD

Part,

Date(AddMonths(Today(), IterNo()), 'MM-YYYY') as Month,

Stock*(pow(1-[Rate of depletion per Year],(IterNo()-1)/12)) as Inventory

Resident Data

While IterNo()<=120;

Re: Forcasting in Qlikview

effinty2112 — Thu, 28 Sep 2017 14:25:00 GMT

Hi Venkat,

Glad you've got the result you need. Please close the thread if there's nothing else i can do for you.

Regards

Andrew

Re: Forcasting in Qlikview

Mon, 02 Oct 2017 20:41:06 GMT

Thanks Andrew