Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Danny_Langley
Employee
Employee

Need help creating a straight line forecast chart in Qlik Sense

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 TypeInitial CostMonthly Recurring CostDate Received
Smart Phone$250$1005/1/15
Tablet$400$901/1/15
Smart Phone
$250
$100
6/1/15
Hotspot$50$7511/1/15
Tablet$350$503/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!

1 Solution

Accepted Solutions
ogster1974
Partner - Master II
Partner - Master II

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.

View solution in original post

3 Replies
ogster1974
Partner - Master II
Partner - Master II

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
Employee
Employee
Author

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
Partner - Master II
Partner - Master II

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