topic Re: Projecting Future Month's Potential Charges in QlikView

Projecting Future Month's Potential Charges

pnn44794 — Wed, 01 Feb 2017 02:16:19 GMT

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.

Re: Projecting Future Month's Potential Charges

kaushiknsolanki — Wed, 01 Feb 2017 05:04:36 GMT

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

Re: Projecting Future Month's Potential Charges

pnn44794 — Sun, 13 Oct 2019 19:08:04 GMT

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.

Re: Projecting Future Month's Potential Charges

pnn44794 — Thu, 02 Feb 2017 13:53:22 GMT

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.

Re: Projecting Future Month's Potential Charges

Anonymous — Tue, 07 Feb 2017 13:59:39 GMT

Hi Perry,

Sample app attached.

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

Hopefully this helps!

Re: Projecting Future Month's Potential Charges

pnn44794 — Tue, 07 Feb 2017 14:37:07 GMT

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.

Re: Projecting Future Month's Potential Charges

Anonymous — Tue, 07 Feb 2017 15:39:31 GMT

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.

Re: Projecting Future Month's Potential Charges

pnn44794 — Tue, 07 Feb 2017 15:49:54 GMT

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

Re: Projecting Future Month's Potential Charges

Anonymous — Tue, 07 Feb 2017 16:07:54 GMT

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?

Re: Projecting Future Month's Potential Charges

Anonymous — Wed, 08 Feb 2017 05:33:09 GMT

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)

Re: Projecting Future Month's Potential Charges

pnn44794 — Sun, 13 Oct 2019 19:03:18 GMT

Hello Deran:

Ok. I’ll make that change as I would like to pull in some history. Right now, I’m having some additional challenges. I should have provided this information from the beginning. My fault for not doing so. I believe I mentioned that I’m loading the data from a database, right?

I have to join 3 tables together in order to get all the data points needed. I can do this just fine running a query from Toad. I’m also, based on those joins, pulling in additional fields.

Any ideas? Thanks in advance.

Re: Projecting Future Month's Potential Charges

Anonymous — Thu, 09 Feb 2017 07:26:51 GMT

Hi Perry,

Your code above has a mix of QV and SQL code so QV will not understand the commands.

Assuming the select statement runs fine on TOAD without errors and to rule out the access issue,

start a fresh qvw and add in your connection string.

Then paste in the code as is:

- Code in bold is the exact SQL statement and regular text is QV code. SQL statement is letting QV know we want to run sql code.

- This will bring in all data returned by the sql

- When debugging you can load top/first 10 records as you would normaly do in TOAD - Just add it in the correct position after the SQL keyword below.

Fact:

Load *;

SQL

SELECT a."APP_ALLOCATION",

a."APP_CNT4INV",

a."APP_ENVIRONMENT",

a."APP_ID",

a."APP_INSTANCE_ID",

a."APP_NAME",

a."APP_RESOLVER",

a."APPLICATION_IDS",

a."APPLICATION_NAMES",

a."ASSET_ENVIRONMENT",

a."BUSINESS_LOAD",

a."CAF_CRITICAL",

a."CC_ALLOCATION",

a."CCB_INFRA_CAT_L1",

a."CCB_INFRA_CAT_L2",

a."CCB_INFRA_CAT_L3",

a."CCB_LOB",

a."HOST_INV_NO",

a."HOST_NAME",

a.INFRA4APP,

a."INVENTORY_NO",

a."OVERALL_RISK_RATING",

a."RECORD_OWNER (SUPPORT_TEAM)",

a."SITE_NAME",

a."SEAL_APP_LOB",

a."SEAL_APP_LOB_NEW",

a."SEAL_TGO",

b."CHARGE_AMOUNT",

b.ORG_CC,

b.ORG_DESCRIPTION,

b.ORG_SORT_CODE,

b.PLATFORM,

b.[PPG_L2 (FAMILY)],

b.[PPG_L3 (PSET)],

b.[PPG_L4 (OFFERING)],

b.PPGUNITS,

b.PRICE,

b.[PRODUCT (AVAILABILITY)],

b.[PROVIDER_L2 (TOWER)],

b.[SCE (FINANCIAL_CODE)]

FROM sources.common."IR_TAMS_APPS_COMMON_FIELDS_BASE_V" a

left join sources.common."IR_MARS_CHARGES_V" b on b.HOST_NAME = a.HOST_NAME

left join sources.common.IR_SEAL_APP_DETAILS_V c on c.APP_ID = a.APP_ID

WHERE (( = '201609' or = '201610' or = '201611' or = '201612') or like '2017%') and ORG_SORT_CODE like 'K%';

Run this just to bring in the data and then take it from there.

Hopefully this helps!

Re: Projecting Future Month's Potential Charges

pnn44794 — Thu, 09 Feb 2017 18:00:45 GMT

Sorry Deran, but I'm not sure where left(chargemonth,4) as Year should go in order to capture any 2018 decommissions. Does it just need to go in one place? Is there any calculation or other manipulation I need to do with it? Thanks in advance.

Re: Projecting Future Month's Potential Charges

Anonymous — Thu, 09 Feb 2017 18:05:37 GMT

Hi,

The code above is just to test the connection. Have you tried it as above? Did it run and return the expected rows?

Re: Projecting Future Month's Potential Charges

pnn44794 — Thu, 09 Feb 2017 18:19:16 GMT

I have not tried it yet, but I will and hopefully today. I was actually responding to one of your earlier responses where I had asked about including / capturing any 2018 planned decommissions. I've been working on an alternative to pulling directly from the database as a temporary measure. I'll explain what I've done in about 30 minutes with another post. I currently have a load running which hopefully will work. The database pull is the preferred method though.

Re: Projecting Future Month's Potential Charges

Anonymous — Thu, 09 Feb 2017 18:56:01 GMT

Ok cool.

Depending on how often your refresh is going to be and the amount of records you are pulling in I would suggest creating a Qv data loader to extract and transform then save to a qvd. Qvds will allow you to hit the dB less especially during testing. Qvds also load way faster than constantly pulling from the dB which will save you lots of dev time. You should also look at loading a subset of data to your discretion to bed down the logic and then test it on the full load.

Once you have created a data loader process, you can work on your application and load faster without pulling from the dB.

Are you able to pm?

Re: Projecting Future Month's Potential Charges

pnn44794 — Sun, 13 Oct 2019 19:05:06 GMT

Hello Deran:

I will definitely try this as pulling from the database is the preferred method over a spreadsheet. In the meantime....

Before I got your suggestion above for trying out the just the SQL code in a new qvw, I had gone down another path which is:

Run the SQL code in Toad and get the results
Export the results to a csv file
Added the additional fields I mentioned before to the appropriate sections of the code
Perform the load

This appears to be working, but I do get a Field Not Found <ChargeYearMonth> error when I run the load. I say it appears to be working because the data loads into the table fine and I do have Charge Month, so it looks like it's working, but I haven't fully validated it yet. Any ideas why ChargeYearMonth would not be found? The error message comes up during the load for the Data section (Data << DataTemp) and after all rows have been fetched.

Re: Projecting Future Month's Potential Charges

pnn44794 — Thu, 09 Feb 2017 19:09:50 GMT

I'm not sure what you mean by "Are you able to pm?" What's pm? Sorry if I'm being dense, but I don't know what pm is.

Re: Projecting Future Month's Potential Charges

Anonymous — Fri, 10 Feb 2017 05:56:16 GMT

Its most probably the naming of the fields are inconsistent.

Its difficult to point out without looking at the script.

PM is private message via the community site. I have email notification setup for this to reduce the emails i receive

Re: Projecting Future Month's Potential Charges

pnn44794 — Fri, 10 Feb 2017 17:05:05 GMT

Gotcha. Thanks. I’ll respond via PM.