I have a need to be able to project potential future costs based on a decommission date of an application being missed. For example, if an application is scheduled for decommission sometime in February and that does not happen, what is the cost for April 2017 to January 2018? April 2017 (201704) through to January 2018 (201801) is not currently present in the data pulled from the database. To be clear, if an app is decommissioned at some point in January 2017, we would still be billed in February 2017 for January. If still live in February, billed in March and billing would stop with April's bill. So, I need to add a +2 to the calculated Charge Month.
Let's say App A is due for decommission in February 2017 and is indeed decommissioned in February 2017, there's no additional charges for 2017 AFTER March 2017 because we're still charged for February (think electric bill where you're not billed for the current month until the next month because there's no way to predict what the current usage is until that month is over and / or billing period is over) with the March bill. If App A were to miss being decommissioned in February 2017, the bill should be the same in April 2017 because we're now into March which would have a charge amount we would see with April's billing (April billing for March usage) because there was partial usage in March.
The fields in play are: "Charge Month" in the format of YYYYMM, "Charge Amount" in dollars, "Decommission Date" in the form of mm-dd-yyyy, the future months which don't exist yet in the format of YYYYMM.
As an example:
Charge Month = 201702, Decommission Date = 06-15-2017, Charge Amount (for current month of analysis, i.e. 201702) = $100
Result:
I would have columns from 201704 thru columns 201801 for Charge Month as part of the straight table showing a Charge Amount of $100 for each Month from Charge Month = 201708 through 201801 based on a Decommission Date of 06-15-2017 ( or any other date in June). The dollar amount would be based on the current months charges, meaning in this case, the current month is 201702 at $100.
Does this make sense? I know it's long winded and my apologies for that.
Do you get notified when you have a new message (PM) in your Inbox? I’m not sure as this is the first time I’m using it. I’m also still pretty new to Qlikview and the community.
First, let me say how much I appreciate your help. Very much so.
For actuals, we started with Charge Month 201701. Starting March 1st, I will also have 201702, April 1st will add 201703 and so on. Billing is always a month behind. I will keep, as of now, 1 month of actuals, because I fear keeping multiple months of actuals will cause the data set to become too large and unwieldy (have 201701 now and should drop off when I have 201702 on 3/1). In the query I use to pull the raw data, I'll specify 201702 on 3/1, 201703 on 4/1, etc. As an FYI, this data will be loaded just once a month.
The split will be one month of actuals to 18 months of projections. Make sense?
14/02/2017 11:34:43: Error: Cannot open file 'J:\Work In Progress\Perry N*****\DataSources\AppToHostWithCharges.csv' The system cannot find the path specified.
