aggr() function issue

[#1]

Hi guys,

I got some questions about aggr() function,I have a sales table just looks like below:

yearid	custid	itemid	purchaseid	amt
2013	A	item1	1	5
2012	A	item2	2	10
2008	A	item3	3	15
2006	A	item4	4	20
2013	B	item2	5	10
2013	B	item3	6	15
2012	B	item2	7	20
2018	B	item1	8	25
2013	C	item1	9	5
2008	C	item2	10	10
2007	C	item3	11	15
2006	C	item5	12	20
2013	D	item5	13	11

What I need is to calculate what items each customer has bought before they first buy the item item2, just look like:

yearid	custid	itemid	AMT	min_year_item2	status	combined_item
2008	A	item3	15	2012	YES	item3+item4
2006	A	item4	20	2012	YES	item3+item4
2007	C	item3	15	2008	YES	item3+item5
2006	C	item5	20	2008	YES	item3+item5
2013	A	item1	5	2012	NO	item1+item2
2012	A	item2	10	2012	NO	item1+item2
2018	B	item1	25	2012	NO	item1+item2+item3
2013	B	item2	10	2012	NO	item1+item2+item3
2012	B	item2	20	2012	NO	item1+item2+item3
2013	B	item3	15	2012	NO	item1+item2+item3
2013	C	item1	5	2008	NO	item1+item2
2008	C	item2	10	2008	NO	item1+item2

Thanks for your help.

[vikasmahajan]

PFA

[Gysbert_Wassenaar]

I think you should solve this in the script. See attached example.

[#4]

Thanks, but what I need is the expression: combined_item,

finally it should be shown as below:

yearid	custid	itemid	min_year_item2	combined_item
2008	A	item3	2012	item3+item4
2006	A	item4	2012	item3+item4
2007	C	item3	2008	item3+item5
2006	C	item5	2008	item3+item5
2013	A	item1	2012	item1+item2
2012	A	item2	2012	item1+item2
2018	B	item1	2012	item1+item2+item3
2013	B	item2	2012	item1+item2+item3
2012	B	item2	2012	item1+item2+item3
2013	B	item3	2012	item1+item2+item3
2013	C	item1	2008	item1+item2
2008	C	item2	2008	item1+item2

[#5 · Accepted solution]

I don't really understand what you are trying to achieve. You want to know what items a customer has bought before he first buys item2. Then why do you expect an output for customer B?

Anyway, try:

Concat(TOTAL <custid> Aggr(If(yearid < (Aggr(NODISTINCT Min({$ <itemid={'item2'}>} yearid),custid)),Only(itemid)),custid,yearid),'+')

[#6]

Thanks,but the min_year_item2 is changed when different period time is selected,so I use the expression:

aggr(NODISTINCT min({<itemid={'item2'}>} yearid),custid) .

Is there any other solution?

[#7]

Thanks Dirk, it works.