Projecting Future Month's Potential Charges - Qlik Community

pnn44794 · ‎2017-01-31

Hello:

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.

kaushiknsolanki · ‎2017-02-01

Dear Perry,

Is it possible for you to share some sample data, which has the exact structure as you said. (Similar data model or table which you are using).

This will help us to understand and try something for you.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

pnn44794 · ‎2017-02-01

Hello Kaushik:

Thanks for your reply and my apologies for the delay in mine. It's been one of those days...... Thanks for your patience.

pnn44794 · ‎2017-02-02

Kaushik:

My apologies. It appears I responded to myself and not you. I have provided additional information. Please see below (or maybe just above this post). Thanks in advance.

Anonymous · ‎2017-02-07

Hi Perry,

Sample app attached.

Projects actual values from 201701 to Decom month + 1 month.

Hopefully this helps!

pnn44794 · ‎2017-02-07

Hello Deran:

Wow! This looks like it does exactly what I was looking for. I didn't think I'd get a response on this as it's not an easy ask and I would have completely understood that. 🙂 So, I really appreciate you taking the time to have a go at this.

I have a very busy day today, but I will try to get this in place and tested today. Definitely this week. I do need to make a few tweaks to the code as the data actually comes from a database, but no big deal for me to do that.

Lastly, once I've tested and everything works (I'm expecting it will), I'd like to send the code back to you with my comments included for each section of code to see if I'm understanding what the code is doing. I know you have comments too in there. I just want to add some more in-depth comments. The reason for that is because I'm hoping to learn something from this and get better at coding in Qlikview.

Is that ok with you? Thanks again.

Anonymous · ‎2017-02-07

Sounds good... The code might be a bit messy as it was a first go and a bit of a hurry. It can also be extended further to get the correct charge year for instance from the charge month field so that it dynamically picks up 2018 if the decommissioned date is in 2018. Should be easy though with what's provided. Also lots of room for improvement as you go along.

pnn44794 · ‎2017-02-07

To pick up / include any 2018 decommissions, what would you change / modify?

Anonymous · ‎2017-02-07

In the data chargemonth is in the format YYYYMM so left(chargemonth,4) as Year...be sure to put this in the correct place..The load statement were we are adding the projected months data.

PM if you are at ms?

Anonymous · ‎2017-02-08

Update to the last line of code:

Remove the num#() function

where NOT Exists([Charge Month]);

This will bring all previous actuals in as well. (201609-201612)