topic Aggragating on a field with no corresponding data in QlikView

Aggragating on a field with no corresponding data

zagzebski — Mon, 18 Jul 2016 17:34:18 GMT

I am trying to do the following - basically in my core data I have a field ("Added this Month") that identifies what month/year a client was added. In my chart I want to create a calculated dimension to check if the customer was new anytime in the current year and if so put a "Yes" in every line of the resulting chart.

DATA:
Customer	Added this Month	Product	MonthYear	Sales
A	Yes	-	201601	-
A	No	Oak	201603	200
A	No	Pine	201603	300
A	No	Cedar	201604	400
A	No	Oak	201605	500
A	No	Pine	201605	600
RESULT I WANT:
Customer	Add	MonthYear	Total Sales
A	Yes	201603	500
A	Yes	201604	400
A	Yes	201605	1100

Re: Aggragating on a field with no corresponding data

swuehl — Mon, 18 Jul 2016 17:59:07 GMT

Maybe like this:

Customer	*=Aggr(If(Sum({<Customer = p({<[Added this Month] = {Yes}, MonthYear = {"2016"}>})>}1),'Add','-'),Customer)**	MonthYear	Sum(Sales)
			2000
A	Add	201604	400
A	Add	201603	500
A	Add	201605	1100

edit:

Using a straight table chart with three dimensions (the second could also be converted to an expression)

Customer

=Aggr(If(Sum({<Customer = p({<[Added this Month] = {Yes}, MonthYear = {"$(=Year(today()))*"}>})>}1),'Add','-'),Customer)

MonthYear

1 expression:

=Sum(Sales)

Re: Aggragating on a field with no corresponding data

swuehl — Mon, 18 Jul 2016 18:09:08 GMT

Another option would be to create the flag in the script, maybe like

Table:

LOAD Customer,

[Added this Month],

Product,

MonthYear,

Sales

FROM

[https://community.qlik.com/thread/225541]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 8))

));

LEFT JOIN

LOAD Customer,

MaxString(If(MonthYear LIKE Year(Today(1))&'*', [Added this Month])) as FlagThisYear

RESIDENT Table

GROUP BY Customer;

Re: Aggragating on a field with no corresponding data

zagzebski — Mon, 18 Jul 2016 18:38:56 GMT

Thanks!