Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone help me get this expression to work?
sum(if([Qty Bearing]=1 and [Year Ordered]>=2008,
if(
count({$<[Year Ordered]={">=2008"}>}DISTINCT [Year Ordered])
<3,0,
[Line Amount])))
I need this to work for a Pivot Table with dimensions
Part,
Part Description,
Product Group,
Part Class.
In a straight table this expression works perfectly. It also works if in the pivot the highest group is Part:
if(count({$<[Year Ordered]={">=2008"}>}DISTINCT [Year Ordered])<3,0,
sum({<[Qty Bearing]={'1'},[Year Ordered]={">=2008"}>}[Line Amount]))
As soon as this is grouped by Product or Class it no longer takes the "limiting expression" into account.
I basically only want to return order amounts if the part has been ordered at least 3 of the past 5 years.
Try limiting your parts to the subset that actually have the count of orders you need, maybe along these lines:
=sum( {<[Qty Bearing]={'1'}, [Year Ordered]={">=2008"}, Part={"=count({$<[Year Ordered]={2008,2009,2010,2011,2012}>} DISTINCT [Year Ordered])>=3"} >} [Line Amount]))
Try limiting your parts to the subset that actually have the count of orders you need, maybe along these lines:
=sum( {<[Qty Bearing]={'1'}, [Year Ordered]={">=2008"}, Part={"=count({$<[Year Ordered]={2008,2009,2010,2011,2012}>} DISTINCT [Year Ordered])>=3"} >} [Line Amount]))
You sir are a genius. I wish I could give you more than just the correct answer. Would have taken me all week and I would never have thought of that.