Monthly Meter Usage

I have a data collection agent that runs a daily sync with 20,000 devices that have meters on them.

One audit pulls the device id and collects any available meter.

Many of my customers ask me for a report that shows their last 24 months of "monthly usage" in a bar chart. They want to visualize their usage and see if it is trending up or down overall, over time.

So one audit gives me a date/time, an equipment id, and any meters, typically black/white and/or color.

If I use a formula like "Maximum({b/w meter},{auditdate},"monthly")-Minimum({b/w meter},{auditdate},"monthly")" I get a pretty accurate trend graph...but it's not perfect

This formula excludes any volume between the last audit of any month and the first audit of the next month.

So if 1/31/2017 shows a meter of 401, and 2/1/2017 shows 408, and 2/27/2017 shows 749, we know we have a total meter usage from inception of 749, but it shows the monthly volume for January is 401, and february is 341...341+401=742. So we lose 7 clicks of the meter. It gets out of hand if people are doing thousands of pages per day..over 24 months.

The reason I am pulling from 2/27/2017 instead of 28th is because sometimes audits fail on the most important day of the month and we have to rely on previous information. So please consider that we're looking for the max date or max meter value of any month, not specific dates.

So my current workaround to this is working well enough, but I feel like it should be a lot easier to get this data into a visual chart.

My formula tests if the month of auditdate of a record equals the month of previous records date. If it does, it returns a value of 0, if not it returns the previous meter value. This test basically takes the maximum of the previous month and puts in in the month being calculated. Then using a formula of "Maximum({b/w Meter},{auditDate},"monthly")-{@ Previous Maximum} I get the monthly usage value.

This basically does all of the math in the first record of the month, so I'm able to suppress details and put these formulas in the header of the group, grouped by audit date monthly.

Since I'm using some conditional formulas, I cannot use the values in a chart because they must be evaluated later. I have to export the data to excel, build a pivot chart/table from template.

Would anyone be willing to take a crack at this to help me get a value of "Monthly usage" that doesn't skip the data between last/first of months and can be charted?

Here is a link to a sample of meter pulls.

https://docs.google.com/spreadsheets/d/1BY0stpbuiMHQrIo3tq0aTphUt3mVrbBtSaQm42fexdU/edit#gid=0

Edited by jmallet74 - 27 Feb 2017 at 5:13am

I've downloaded your data and I'll take a look at it. How are you pulling the data into your report? Is it in an Excel file or tables in the database? If it's in tables, are you just joining tables together in your report? Or do you have a stored proc, view, or command for getting the data?

-Dell

I would recommend using a command for this - I think that's the only way you're going to be able to do what you need. A command is just a SQL Select statement. For information about how to work with them correctly in Crystal see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

Working with just a single table that has the meter data, here's what the command I wrote looks like:

Select auditMonth, auditYear, EquipID,

  max(DoubleClickMono) monoMax, max(DoubleClickColor) colorMax,

  min(DoubleClickMono) monoMin, min(DoubleClickColor) colorMin

from (

  Select lme.auditMonth, lme.auditYear, 

    md.EquipID, md.DoubleClickMono

    md.DoubleClickColor, md.AuditID

  from MeterData md

    inner join (

      Select

        case MONTH([Audit Date])

          when 1 then 12 

          else MONTH([Audit Date])-1 

        end auditMonth, 

        case MONTH([Audit Date])

       when 1 then YEAR([Audit Date])-1

       else YEAR([Audit Date]) 

        end auditYear, 

        max([Audit Date]) maxDate

      from MeterData

      where [Audit Date] <= GetDate()

        and [Audit Date] >= DATEADD(yy, -2, GetDate())

      group by MONTH([Audit Date]), YEAR([Audit Date])

    ) lme

    on md.[Audit Date] = lme.maxDate



UNION



  Select

    MONTH(md.[Audit Date]) auditMonth, YEAR(md.[Audit Date]) auditYear, 

    md.EquipID, md.DoubleClickMono, md.DoubleClickColor, md.AuditID

  from MeterData md

  where [Audit Date] <= GetDate()

    and [Audit Date] >= DATEADD(yy, -2, GetDate())

) meterAudit

group by auditMonth, auditYear, EquipID

You would then create a formula that would append the year and month into a string. In order to get things to sort correctly, it would look like either one of the following:

Text
ToText({command.auditYear}, 0, '') + ToText({command.auditMonth}, 0, '')

Date
Date({command.auditYear},{command.auditMonth}, 1)

-Dell



Edited by hilfy - 01 Mar 2017 at 9:19am