You indeed have overlapping intervals, so I think you should clarify how to handle the 1 day your intervals overlap (which value to take for that date). That's the reason why you get the outlier at those days when using the sum(Value) expression.
For example, if you want to use the value from the ending interval, you could use [I had some problems loading your excel file, so you might need to adjust the first load]:
TMPPRODUCT:
LOAD Product,
[m max unit/h],
unit,
[valid from],
[valid until],
[mmax from here]
FROM
Mmax_PEOL_v3.xls.xlsx
(ooxml, embedded labels, table is Sheet1);
Product:
NOCONCATENATE LOAD Product,
[m max unit/h],
unit,
if(Peek(Product)=Product and peek([valid until])=[valid from], [valid from]+1, [valid from]) as [valid from],
[valid until],
[mmax from here]
resident TMPPRODUCT order by Product, [valid from];
drop table TMPPRODUCT;
Calendar:
LOAD
Date(makedate(2010)+RecNo()-1) as Date
autogenerate 1000;
IntervalMatch (Date) Load [valid from], [valid until] resident Product;
and if you want to use the value from the coming period, you need to change the sort order of [valid from] and change the LOAD to
Product:
NOCONCATENATE LOAD Product,
[m max unit/h],
unit,
if(Peek(Product)=Product and [valid until]=peek([valid from]), [valid until]-1, [valid until]) as [valid until],
[valid from],
[mmax from here]
resident TMPPRODUCT order by Product, [valid from] desc;
That will correct your absolute value display per Date and Product, but that was not the problem with the relative display, we need to use [valid from] as sort weight to FirstSortedValue, use
=[m max unit/h] / FirstSortedValue(total<Product> [m max unit/h], [valid from])
as expression in your chart.
I am attaching the sample app, though you can't really use it with your personal edition without losing a recovery attempt.
Regards,
Stefan
