Production dates aggregated in hourly average

[Bart_Breekveldt]

Dear community, 

Above I made an graph with the average production per hour. A '7' for example gives the average production between 07.00.00 and 07.59.59. I made it with an selfmade dimension 'Hour of production': hour(Date & Time). The purple/dark blue line gives the total amount, the others are the individual production lines. However, I want the average only for production dates (dates where at least 100 units were produced, to exclude tests).

Therefore I have aggregated the days which are production days like this: 

=IF(Aggr(SUM(Etiketteringsbandenverschil),[Datum-Tijd.autoCalendar.Date])>100,'Production_Dates')

The expression for the graph at the moment is as follows (measures come in every minute)

Avg(Etiketteringsbandenverschil)*60 (If there is a better way, please let me know, the hour function didn't work in the graph itself)

How can I integrate these two in the expression of my graph with the hour dimension?

Thanks in advance

[sunny_talwar · Accepted solution]

Have you tried this?

Dimension (assuming this is your hour field that you created using Date & Time field

Production_Dates

Expression

Avg({<[Datum-Tijd.autoCalendar.Date] = {"=Sum(Etiketteringsbandenverschil) > 100"}>} Etiketteringsbandenverschil) * 60

[Bart_Breekveldt]

Thanks, indeed 'Datum-Tijd' (Dutch)  translates to Date-Time in English