Workflow Help

[Brian_H]

Hello,

I am working with two very large data sets containing information on thousands of locations. I have provided two example data sets of the two files I am working with. These are the outputs after about 50 other tools are used to get the data into this format.

Now what I am trying to do is to take the workflow to the next step and this is where I am struggling. I am looking to understand the improved efficiency of a location from a utility bill cost and kWh usage. I would like to average the Power Bill before and after the Equipment Updates. The Month/Year of the Equipment Updates vary so I need the workflow to look at the Location average usage up to the Month/Year before the Equipment update and output one result. Then once the workflow notices an Equipment Update took place, Month/Year, ignore that Month/Year (because the days of the month also vary) and output the new Location average from the next month after that point and beyond.

Thanks for the Help!

Brian

[AmeliaG · Accepted solution]

Hi Brian,

I've used the sample data you sent to build out this example. This is the approach I took:

1. Join equipment change data with power usage using the Location as the key field

2. Add a flag to indicate if billing month is the same as the month the equipment was changed

3. Use this flag to add a 'period' id. When the equipment is changed, we go into a new period. 

4. Use the flag to filter out the months when the equipment was changed. 

5. Use the summarize tool to aggregate the average costs / usage for each period. 

Hope this helps!

Amelia

[Brian_H]

Hello Amelia,

Thank you for your help! This is working just as I need it to in my larger data sets.

Thanks

Brian