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
解決済! 解決策の投稿を見る。
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
Hello Amelia,
Thank you for your help! This is working just as I need it to in my larger data sets.
Thanks
Brian