Data estimation

Question

Hello everybody,

I am trying to estimate consumption data, by applying three methods (only one or a combination). I need to estimate only a max between 20% of the total period for which actual data is reported (for the two whole years that I am interested in 2020-2021) and 93 days. We can have consumption outside the active period for one meter (it’s historical data), but I estimate only the active period, within the two years. The methods are:

* If data missing for one month is 15 days or less - estimated consumption will be equal to actual consumption/number of days for which we have consumption*missing number of days.
* If we have missing for example four months' worth of data for 2020 (Jan-April), but we have full data for 2019, then we compare the consumption for May-Dec 2020 with 2019 and we apply the increase/decrease percentage for 2019 Jan-April data to get the estimated one, but limit to 93 days or 20%.
* Average monthly consumption with no previous year: we get the total actual consumption/days for which we have data*missing number of days, but limit to 93 days or 20%.

I managed to apply the first method by generating the days we have consumption and the missing days, but I struggle with the other two methods. I have attached the flow with dummy data.

I hope that somebody can help me with a solution or to pinpoint in a direction.

Data Estimation.yxzp

InputDate.yxdb

RMojsiak · Answer

I was thinking of that, but it gets complicated because the analysis is at a day level, not at the month. I used generate rows, to get all the days for which I had consumption and I did the join, as you mentioned, but the problem occurs when, for example, for the previous year I have missing periods in the middle of the month, and I would need the whole month. Maybe I'm complicating myself...

JarekSkudrzyk · Answer

@RMojsiak

hi, regarding methods:
2. you can count the records for each month (or use unique tool) in 2020 and if there are only 8 records (4 months worth of data is missing) you can join the data on month and year / year + 1 with previous years' data
3. if filter on (year - 1) gives 0 records (the number of records calculated with summarize tool) - then apply the logic you described for calculating the consumption.
I wrote it in somewhat concise way (maybe too concise) - hope it helps. If you need further assistance - please let me know