Need help creating a straight line forecast chart in Qlik Sense

[Danny_Langley]

Hi all,

I am trying to create a 5-yr forecast line chart with IT asset data.

This is an example of the data.

Device Type	Initial Cost	Monthly Recurring Cost	Date Received
Smart Phone	$250	$100	5/1/15
Tablet	$400	$90	1/1/15
Smart Phone	$250	$100	6/1/15
Hotspot	$50	$75	11/1/15
Tablet	$350	$50	3/1/15

In theory, I would have 3 lines (for each device type) on the chart going out 5 years.

Was thinking I would create a master calendar with 5 years of future months past the max date in my data set and somehow associate it to the monthly recurring costs. I feel like this should be fairly straight forward as the monthly recurring cost will be the same each month for each asset, but I can't figure out how to associate the original table with the cost data to the master table (I forgot to include this but there would be an asset ID as well for each row) Also, the additional wrinkle is including the initial cost into the calculations...although this probably isn't quite as crucial for inclusion if it's a pain to do.

Any help would be appreciated!

[ogster1974 · Accepted solution]

I'd tackle this from a data model point of view

A assets has a name an expected life (in months), renewal dates, licences and a type which you can store in one table and then link to facts about.

These facts include different types of costs estimated and actual, ownership, location etc...

Initial purchase cost  and monthly running costs (note this could be fixed or variable over the life of the asset).  I would build up these datasets either in the source data, excel,sql etc. by month. this would allow you to plot variable costs and give you a more flexible and accurate forecast model.

Finally for asset management its important to note any asset dependencies which you can do by adding a parent asset key to your model so you can identify what the total impact of an assets failure, renewal might be.

[Danny_Langley]

Hey Andy,

Sorry for my incredibly late response! Thanks so much for your feedback. What you mentioned makes total sense and based on other things I've been working I haven't had a chance to fully go down that path. However, when I do, I'll let you know how it goes!

[ogster1974]

Please do.  If I can help any more let me know.