aggr function only resolving one calculation

[tyember1]

Hello,

I am trying to display a ratio for each item in a pivot table. the items are buried under shift date -> shift code -> line -> item.

here is what the result looks like:

the formula takes what is produced for that specific line under the criteria then divides it by a ratio of standard run rate and "earned" line hours (based on lbs produced compared to run rate /hr)

This is my formula:

aggr(NODISTINCT
(aggr(sum({$< [Data Type] = {'Volume'} >} [Total Quantity LBs]),ItemKey,ProductionLineID,[Shift Code],[Shift Date]))

/

((sum(aggr((Sum( {$< [Data Type] = {'Volume'} >} [Total Quantity LBs])) /
(sum([WIP Productivity (lbs/hr)])*
(1-(([Waste % Per Prophix])+([Downtime % Per Prophix])))),ItemKey,ProductionLineID,[Shift Code],[Shift Date])))

/

(sum(total <ItemKey,ProductionLineID,[Shift Code],[Shift Date]> aggr(
sum(aggr((Sum( {$< [Data Type] = {'Volume'} >} [Total Quantity LBs])) /
(sum([WIP Productivity (lbs/hr)])*
(1-(([Waste % Per Prophix])+([Downtime % Per Prophix])))),ItemKey,ProductionLineID,[Shift Code],[Shift Date]))
,ProductionLineID,[Shift Code],[Shift Date])))

*

sum(total <ProductionLineID,[Shift Code],[Shift Date]> aggr(
sum(aggr(sum([Uptime Minutes])/60,ProductionLineID,[Shift Code],[Shift Date])),ProductionLineID,[Shift Code],[Shift Date]))

*

([WIP Productivity (lbs/hr)]))

,ItemKey,ProductionLineID,[Shift Code],[Shift Date])

Thanks for your help!

[sunny_talwar]

It might be easier to help if you are able to share a sample.

[tyember1 · Accepted solution]

I am not sure how, but I resolved this issue. Sorry Sunny I cannot share sample data. Thank you for your response though.