Top with Aggr across multiple products and differing live dates

[Amen_90]

I was hoping someone might be able to guide / help me. So I want to create an index chart for product sales which is based on live date that can be variable across different customers and products. 

Currently I can get the top value across the range of sales for the data. using the following expression:

aggr(Top(Sum(Sales)),Product, (Date,(Numeric, ASCENDING)))

Output

Date	Live_Date	Account	Product	Sales	Top
Jan-22	July-22	A	Apple	1	1
Feb-22	July-22	A	Apple	2	1
Mar-22	July-22	A	Apple	3	1
Apr-22	July-22	A	Apple	4	1
May-22	July-22	A	Apple	5	1
Jun-22	July-22	A	Apple	6	1
Jul-22	July-22	A	Apple	7	1
Aug-22	July-22	A	Apple	8	1
Sep-22	July-22	A	Apple	9	1
Oct-22	July-22	A	Apple	10	1
Nov-22	July-22	A	Apple	11	1
Dec-22	July-22	A	Apple	12	1
Jan-22	July-22	A	Samsung	2	2
Feb-22	July-22	A	Samsung	4	2
Mar-22	July-22	A	Samsung	6	2
Apr-22	July-22	A	Samsung	8	2
May-22	July-22	A	Samsung	10	2
Jun-22	July-22	A	Samsung	12	2
Jul-22	July-22	A	Samsung	14	2
Aug-22	July-22	A	Samsung	16	2
Sep-22	July-22	A	Samsung	18	2
Oct-22	July-22	A	Samsung	20	2
Nov-22	July-22	A	Samsung	22	2
Dec-22	July-22	A	Samsung	24	2

However, what I want is the Top value since the live data (in this example July-22), so the table would look like this:

Date	Live_Date	Account	Product	Sales	Top
Jan-22	July-22	A	Apple	1	-
Feb-22	July-22	A	Apple	2	-
Mar-22	July-22	A	Apple	3	-
Apr-22	July-22	A	Apple	4	-
May-22	July-22	A	Apple	5	-
Jun-22	July-22	A	Apple	0	-
Jul-22	July-22	A	Apple	7	7
Aug-22	July-22	A	Apple	8	7
Sep-22	July-22	A	Apple	9	7
Oct-22	July-22	A	Apple	10	7
Nov-22	July-22	A	Apple	11	7
Dec-22	July-22	A	Apple	12	7
Jan-22	July-22	A	Samsung	2	-
Feb-22	July-22	A	Samsung	4	-
Mar-22	July-22	A	Samsung	6	-
Apr-22	July-22	A	Samsung	8	-
May-22	July-22	A	Samsung	10	-
Jun-22	July-22	A	Samsung	12	-
Jul-22	July-22	A	Samsung	14	14
Aug-22	July-22	A	Samsung	16	14
Sep-22	July-22	A	Samsung	18	14
Oct-22	July-22	A	Samsung	20	14
Nov-22	July-22	A	Samsung	22	14
Dec-22	July-22	A	Samsung	24	14

Currently I'm trying with the following expression, but am not having much luck:

Aggr(top(Sum(if(Date>= Live_Date, Sales))),Product,(Date,(Numeric, ASCENDING)))

Bearing in mind that I have multiple accounts which all have different live dates so I cannot hard code the date as a set.

Any help is greatly appreciated. 

[vinieme12 · Accepted solution]

try below

FirstSortedValue(total <Product> aggr(if(Date>= Live_Date, Sales),Date,Product), aggr(if(Date>= Live_Date, Date),Date,Product))

OR

FirstSortedValue(total <Account,Product> aggr(if(Date>= Live_Date, Sales),Date,Product,Account), aggr(if(Date>= Live_Date, Date),Date,Product,Account))

Load MonthStart(date#(Date,'MMM-YY')) as Date,MonthStart(date#(Live_Date,'MMMM-YY')) as Live_Date,Account,Product,Sales,Top inline [
Date,Live_Date,Account,Product,Sales,Top
Jan-22,July-22,A,Apple,1,-
Feb-22,July-22,A,Apple,2,-
Mar-22,July-22,A,Apple,3,-
Apr-22,July-22,A,Apple,4,-
May-22,July-22,A,Apple,5,-
Jun-22,July-22,A,Apple,0,-
Jul-22,July-22,A,Apple,7,7
Aug-22,July-22,A,Apple,8,7
Sep-22,July-22,A,Apple,9,7
Oct-22,July-22,A,Apple,10,7
Nov-22,July-22,A,Apple,11,7
Dec-22,July-22,A,Apple,12,7
Jan-22,July-22,A,Samsung,2,-
Feb-22,July-22,A,Samsung,4,-
Mar-22,July-22,A,Samsung,6,-
Apr-22,July-22,A,Samsung,8,-
May-22,July-22,A,Samsung,10,-
Jun-22,July-22,A,Samsung,12,-
Jul-22,July-22,A,Samsung,14,14
Aug-22,July-22,A,Samsung,16,14
Sep-22,July-22,A,Samsung,18,14
Oct-22,July-22,A,Samsung,20,14
Nov-22,July-22,A,Samsung,22,14
Dec-22,July-22,A,Samsung,24,14
];

[Amen_90]

Thank you @vinieme12 ! Works perfect!