topic Re: Availability help in QlikView

Availability help

Wed, 23 Mar 2016 19:51:13 GMT

I'm having problems calculating availability for some equipment's. I've got some help how to make reference dates of my events in this thread (Availability) Calendar based on two dates. The problem I'm facing now is that I want to make a Statistical process control chart and it's hard calculating equipment that hasn't got an event on it (100% availability). Is the best way to do a date range for each equipment's? If so, should I calculate the duration of a day - event duration for each day to prevent having two tables?

In this example there's only a few equipment's, but in reality there is about 40000 equipment's, therefore keeping the data to a minimum is a must.

Any help would be much appreciated!

Re: Availability help

swuehl — Wed, 23 Mar 2016 20:51:57 GMT

Maybe like attached?

I created a master calendar in the script (you can adapt the min and max date, currently it uses min and max date of events).

Then you can calculate the availability by calculating the theoretical max time available (see the denominator in above expression), by counting the total equipments in the data and multiply by number of seconds per date.

Re: Availability help

swuehl — Wed, 23 Mar 2016 20:58:59 GMT

And use set analysis in the total equipment count to prevent the chart to collaps to zero when making a selection:

1-Sum(Duration_sec) / (COUNT({1} TOTAL DISTINCT equipment)*Count(DISTINCT EventDate)* 60*60*24)

Re: Availability help

Thu, 24 Mar 2016 07:26:09 GMT

Thanks for a quick answer! I've solved this problem, just forgot to write it down. But when you're using a Statistical process control chart your using avg.

The Aggr() function won't work for the equipment's that has no dates.

From the real application:

But when I choose a equipment that is has 100% I get "No data to display".

Re: Availability help

swuehl — Thu, 24 Mar 2016 10:13:14 GMT

Not sure what you are talking about, have you maybe forgot to add the visualization chart to your sample file?

Without knowing the exact dimensions and expressions you are using, I think I can't offer any specific help.

Re: Availability help

Thu, 24 Mar 2016 11:51:45 GMT

Sorry for being fuzzy, hopefully I can explain myself a bit better.

What I want to able to do is looking at one specific equipment and see the availability for that one. I also want to be able to choose a date range even if it's an equipment that has no events on it. As you can see in the new copy I lose months when I choose "equip2".

I also want to be able to make an SPC chart, one of the expressions for that chart looks something like this:

AVG(TOTAL Aggr(EXPRESSION,DIMENSION))+(2.66*AVG(TOTAL Aggr(fabs(EXPRESSION-Above(total EXPRESSION)),DIMENSION)))

Re: Availability help

swuehl — Thu, 24 Mar 2016 14:17:22 GMT

Ok, I think you would need to create a single record for each equipment (with no outage durations) in your Events table, but then use a link table to link the single records to all dates in your calendar.

This way every machine is present for evey date in your date range.

Re: Availability help

Tue, 29 Mar 2016 06:00:16 GMT

Looks great! Thank you so much!