Aggragating on a field with no corresponding data

[zagzebski]

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

[swuehl · Accepted solution]

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)

1)

Customer

2)

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

3)

MonthYear

1 expression:

=Sum(Sales)

[swuehl]

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

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

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;

[zagzebski]

Thanks!