There is a machine which is monitored throughout the day whose name is captured in dim [machine] at 1hr intervals for a month in dim [date time] and the monitored value goes in dim [measure] which is a measure.
Now, I only want to show the average of the min values(say avg of 30 values one for each day); in one line against the [machine] dim without taking the [date time] dim in the block.
Can someone suggest a way to achieve this? since, taking the below formula against [machine] is giving the min value from the entire month and not aggregating anything.
=Average (Min([measure])) ForAll ([Formated date])
where [Formated date]=ToDate(FormatDate([date time];"dd-Mmm-yyyy");"dd-Mmm-yyyy")
<since this would select min value from each day out of the 24 values one for each hour> but this works only when I drag the formatted date in the block.
Can anyone please let me know what am I missing here ? Thanks.
PS : I am trying this in SAP BO3.1 SP3