I have created a "Call Logging database" in which IT calls are logged. I could able to calculate the time taken to resolve a call. It appears in a view as a column value for each call.
Now I want to calculate the total % monthly uptime of a particular device by totaling the time taken in resolving the each call of that category in minutes and then subtracting it from total minutes in that month, dividing it by 100.
else we need to do the calculations when u save the document and update remaning documents.
event: QuerySave
0
PrashantbhatnagarAuthor Commented:
There is no way in which we can extract the total value from the column and then calculate in a separate form by using DBColumn Or DBLookup formula and do the mathematical calculation in the form....?
Regards
There is method. But when u create a new document and saves it, what values it should show for old documents and newly created dcouments?
0
PrashantbhatnagarAuthor Commented:
What I mean to say is In Every View there is a Column Total option...as soon as you create a new document time will automatically get added in the total..Extract this total from the view and do the mathematical manipulation..Is it possible?
Here's a quick and dirty way to get coarse numbers. Have two forms -- downtime report, LengthOfMonth. For each resource, start by creating, at the beginning of each month, a LengthOfMonth for that category (you could tehcniclaly have a single LengthOfMonth for each month that lists all resource categories). You can just input the date and allow the following formula to calculate minutes:
REM "Assuming the month field is call MONTHYEAR";
first := @Adjust(MONTHYEAR; 0; 0; 1-@Day(MONTHYEAR); 0; 0; 0);
next := @Adjust(first; 0; 1; 0; 0; 0; 0);
last := @Adjust(next; 0; 0; -1; 0; 0; 0);
days := @Day(last);
minutes := days * 24 *60;
You can do that more efficiently of course, but I want you to underastand how the formula works.
Now, categorize on MONTHYEAR, then resource name, then form, and sum on % of parent category. For months where downtime is relatively small to total time, this wll be accurate. For example, a 30 day month with one hour and 15 minutes of downtime is:
(60+15) / (30 * 24 * 60) =
75 / 43200 = 0.0017361
This actually calculates 75/(43200+75)=0.0017331
That's a very small difference. If you can live ith it, this will allow you to build the whole thing in a few minutes. Otherwise, you will have some complex work to do this manually, icnluding gathering the numbers from a similar view, calculating out teh month yoruself, and writing output into your own UI.
0
PrashantbhatnagarAuthor Commented:
Calculating for individual document is not a problem. How to add if there is more then one failure in a month.
Can we somehow extract the total column value and store it in some field or use it by some means. I mean to say add all the minutes taken by each failure by using Column total option and use this total to generate the %?
0
PrashantbhatnagarAuthor Commented:
If It is difficult to do automatically in a view, we can try creating a button to this..