Calculate downtime with incident data

[#1]

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

[Jason_Michaelides]

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

[#3]

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...

[Gysbert_Wassenaar]

Maybe like this?

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

[#5]

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

[Gysbert_Wassenaar]

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

[#7]

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

[Gysbert_Wassenaar]

Ok, how about this one then?

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

[#9]

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.