topic Re: Calculate downtime with incident data in QlikView

Calculate downtime with incident data

Mon, 15 Oct 2012 17:10:22 GMT

Hi!

I'm loading incidents and want to calculate procentage.

Now I load a fact table with date, department, channel and downtime and a calendar as usual plus uptime.

With no selections it's correct but as soon as I select a department uptime gets wrong due to the fact that incidents don't appear every day and only assosiative uptime get's calculated.

How to solve this the best way?

Regards

thomas

Re: Calculate downtime with incident data

Jason_Michaelides — Mon, 15 Oct 2012 17:38:43 GMT

Sounds like you need to associate your departments with both the uptime and the downtime. Maybe by concatenation or a composite key. Can you post a screenshot of your current data model?

Jason

Re: Calculate downtime with incident data

Mon, 15 Oct 2012 18:39:41 GMT

Original post now containing a qvw.

Downsized example, in reality I have 150 departments and data over three years.

I think the solution (or at least one solution) is to create dummies in the fact table with uptime (instead of having it in time table), but how loop through all departments and channel combos?

For channel Internet Uptime is 24 hours * 60=1440 minutes á day so that's easy, for channel Office it's more complicated as we have to concider holiday's and weekends. I'll get back to that...

Re: Calculate downtime with incident data

Gysbert_Wassenaar — Mon, 15 Oct 2012 18:53:13 GMT

Maybe like this?

=(1-(sum(Downtime)/sum({1} I_Uptime)))*100

Re: Calculate downtime with incident data

Mon, 15 Oct 2012 19:02:34 GMT

Well Gysbert, it works with this sampel data but just add these lines to the inline load and choose december and Internet and then that don't work any more...

2011-11-06, ASPA, Internet, 56

2011-11-10, BASP, Office, 20

Re: Calculate downtime with incident data

Gysbert_Wassenaar — Mon, 15 Oct 2012 19:18:20 GMT

Why not? Did you change your hardcoded truth to reflect the days in november? Or do you want the uptime percentage calculated per month?

Re: Calculate downtime with incident data

Mon, 15 Oct 2012 19:23:42 GMT

Yes, I can choose any year and month and the calculations should match that.

Re: Calculate downtime with incident data

Gysbert_Wassenaar — Mon, 15 Oct 2012 19:42:48 GMT

Ok, how about this one then?

=(1-(sum(Downtime)/sum( {1< Month={'=only(Month)'}, Year={'=only(Year)'} >} I_Uptime) ))*100

Re: Calculate downtime with incident data

Mon, 15 Oct 2012 20:32:44 GMT

Close, but I would like to add Channel={'=only(Channel)'} and the same for Dept so that I can put it in a pivot.

Tried, didn't work.