Forcasting in Qlikview

[#1]

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

[effinty2112 · Accepted solution]

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

[effinty2112]

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

[#4]

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

[#5]

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;

[effinty2112]

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

[#7]

Thanks Andrew