Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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;
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
Thanks Andrew