topic Re: Set Analysis with Aggr looking beyond current selection in QlikView

Set Analysis with Aggr looking beyond current selection

Tue, 18 Nov 2014 22:30:57 GMT

Hi all,

We're running into a problem using set analysis in conjunction with the aggr() function. Our transaction data set contains the fields [Product], [Month], and [Dollar]. Our goal is to produce a visual that indicates whether a product is "New" or "Old" based on the selected [Month]. If the product does not exist in the selected [Month] but exists in any month after, the product should be labeled "New".

Below is the desired workflow and result:

- Product A has transactions in July 2013 but not any other month

- I select "May 2013"

- Expression looks in "May 2013", does not find Product A, but finds Product A in July 2013

- Expression classifies Product A as "New"

- Construct a visual with all products labeled as "New" or "Old" with logic above

We've been using a combination of the aggr() function, set analysis, and if conditions to achieve this, but without much success. The main problem, we suspect, has to do with aggregates in set analysis. When we select "May 2013", Qlik seems to only look at the products that exist in the current selection of "May 2013". As a side note, we will have multiple filters, i.e. "Customers" and such.

Would someone have an idea how to do this? Attached at the bottom is our expression for this particular table column. We've verified that the inner set analysis works properly:

SUM( {$<[Month]= {">$(=MAX([Month]))"}>} [Dollars] )

Would the fix have to do with an outer aggregate? If so, how might we implement that?

Thanks

aggr(
if (
	Sum([Dollars]) <= 0
	AND
	SUM( {$<[Month]= {">$(=MAX([Month]))"}>} [Dollars] ) >= 0, 'New',
	if (
	Sum([Dollars]) >= 0
	AND
	SUM( {$<[Month]= {">$(=MAX([Month]))"}>} [Dollars] ) <= 0, 'Old',
	Null())),

[Product Name])

Re: Set Analysis with Aggr looking beyond current selection

MK_QSL — Tue, 18 Nov 2014 22:48:26 GMT

Your Max(Month) is resulting in Numeric Format while your Month may be in MMM format.

Try as below

=SubField('$(MonthNames)',';',Max(PDMonth))

aggr(
if (
	Sum([Dollars]) <= 0
	AND
	SUM( {$<[Month]= {">$(=SubField('$(MonthNames)',';',Max(PDMonth)))"}>} [Dollars] ) >= 0, 'New',
	if (
	Sum([Dollars]) >= 0
	AND
	SUM( {$<[Month]= {">$(=SubField('$(MonthNames)',';',Max(PDMonth)))"}>} [Dollars] ) <= 0, 'Old',
	Null())),

[Product Name])

Re: Set Analysis with Aggr looking beyond current selection

Tue, 18 Nov 2014 22:57:23 GMT

Thanks for your reply. I don't think this is the case though. Just to clarify, our expression:

SUM( {$<[Month]= {">$(=MAX([Month]))"}>} [Dollars] )

Is working properly, so I wouldn't think date formatting is our problem here

Re: Set Analysis with Aggr looking beyond current selection

MK_QSL — Wed, 19 Nov 2014 07:13:30 GMT

Can you please load your sample apps or sample data?