Hello,
I have a yearly timeseries in hourly format. I want to split it into two, one of them containing everything below half of the max value (highest hourly value during the year) and one containing everyting above the same threshold. Example below, with threshold 11,
10 -> 10 0
22 -> 11 11
12 -> 11 1
9 -> 9 0
Splitting the series is not a problem, it's easily done with two if-statements. However, if I use IF-statements the aggregation when I show "month" and "Year" is not correct. The if-statement is then performed on the sum of the first series, meaning (for the example) that it sees if (10 + 22 + 12 + 9) <= 11 and gives the result 11 and 42, while I do want it to show 41 and 12.
If I write an set analysis using
Sum ( {< Timeseries={'<11'} >} Timeseries ) + Sum ( {< Timeseries={'>=11'} >} 11 )
it works, but as soon as I want the threshold (11) to be dynamic I get stuck. I've tried writing the threshold as
(0.5*(MAX(total <Customer> Timeseries )))
That returns the correct threshold but as soon as I use SUM, it generates an error.
Please help. How Do I split a column containing a timeseries into two columns, split by a dynamic threshold - which is correctly aggregated on the time-axis?