Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have created an expression to count the number of workcenters which are having capacity for a week greater than the maxcapacity. Below is the expression i have used to calculate at the week level.
=Sum({<[Year Period]=>}Aggr(if(Sum({<[Capacity Week]={'$(vMaxCapacityWeek)'},[Year Period]=,[Capacity Year]={'$(vMaxCapacityYear)'}>}[Start Quantity]) >
((Sum({<KPI_Filter={'Capacity'},
[Capacity %DateKey]={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},
[Year Period]=>}[Start Quantity])
/
Count({<[Capacity %DateKey]={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},[Year Period]=>} distinct [Capacity Year-Week]))*1.25),1,0),[Work Center]))
Now i want to repeat the same expression to calculate it for each week greater than or equal to maxweek in the current month.
I'd suggest to perform this calculation in the load script rather than in one or more heavy-weight expressions.
@Anuhya_kotha perhaps this
=Sum({<Month={"$(=Max({<Week={"$(=Max(Week))"}>}Month))"}, [Year Period]=>}Aggr(if(Sum({<Month={"$(=Max({<Week={"$(=Max(Week))"}>}Month))"}, [Capacity Week]={'$(vMaxCapacityWeek)'},[Year Period]=,[Capacity Year]={'$(vMaxCapacityYear)'}>}[Start Quantity]) >
((Sum({<Month={"$(=Max({<Week={"$(=Max(Week))"}>}Month))"}, KPI_Filter={'Capacity'},
[Capacity %DateKey]={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},
[Year Period]=>}[Start Quantity])
/
Count({<Month={"$(=Max({<Week={"$(=Max(Week))"}>}Month))"}, [Capacity %DateKey]={">=$(=vCapacityHistoryMinDateKey)<=$(=vCapacityHistoryMaxDateKey)"},[Year Period]=>} distinct [Capacity Year-Week]))*1.25),1,0),[Work Center]))
Bold one is added now, and the pink is optional in some cases.