Skip to Content
0

How to generate theI need to cre percentage of days used in a month for a two month rolling report.

Nov 21, 2017 at 11:11 AM

23

avatar image
Former Member

Hello,

I need to some how generate a percentage for the number of days individual computers have been used in a given month and a separated figure for the month before.

I also needed to show the average running time for each computer in a given week. I have figured out how to do this part without any issues by creating a function to work out the number of days in the present and previous month, telling the total usage time for each computer through grouping by computer ID and then some generic functions using a date parameter.

Effectively below is what I want to produce but can not figure out how to produce the final two columns % of days used (which was created in Excel by someone else and was a aggregation of a larger dataset)

The above was created from a dataset where the date entry for a single computer's usage may not be unique (used multiple times in a single day) which can be overcome by using Groups and a distinct count. However, the main issue comes when working with the current and previous month as Crystal reports will create a blank cell or defaulted value in either this period or last period, what every one will not be populated for date. I.e for a date in this month the number of hours the computer was used would be populated in the this period column but a blank cell would be in the last period column. Now the issue is that a distinct count will still count a blank cell or default date entry if one can not be generated. Giving me a count of 1 higher than I need. In an ideal would I would just be able to take 1 from any count produced in this way and then produce the result I want. However, if a computer is only used say once in a two month period then decreasing the count for each period would cause on of them to be off by 1.

Is there anyway to get around this/ have I gone the longest possible way for a drink of water?

Many thanks in advance.

examples.png (13.0 kB)
10 |10000 characters needed characters left characters exceeded
Former Member

NOW SOLVED

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers