topic Aggr/Set Analysis/ Nested help? in QlikView

Aggr/Set Analysis/ Nested help?

Tue, 28 Feb 2012 16:08:16 GMT

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.

Aggr/Set Analysis/ Nested help?

swuehl — Tue, 28 Feb 2012 16:25:40 GMT

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]))

Aggr/Set Analysis/ Nested help?

Tue, 28 Feb 2012 16:37:15 GMT

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.