How to extract date when sales value reach exactly 100k?

[anindya_manna]

Hello Experts,

I am creating a chart with the columns id ,name ,sales,100K break date.I want to show id and names whose sells more than or equal to 100k and also the aook break date in which it exactly touch 100k milestone.

How to do it in a single table?

[Digvijay_Singh]

Can you share sample data to try on?

[anindya_manna]

d	Name	Process Date	Sales Amount
1	xxx	1/1/2018	200
1	xxx	1/2/2018	200
1	xxx	1/3/2018	200
1	xxx	1/4/2018	200
1	xxx	1/5/2018	200
1	xxx	1/6/2018	200
1	xxx	1/7/2018	200
2	YYY	1/1/2018	300
2	YYY	1/2/2018	300
2	YYY	1/3/2018	300
2	YYY	1/4/2018	300
2	YYY	1/5/2018	300
2	YYY	1/6/2018	300
2	YYY	1/7/2018	300

I want to display this

Id	Name	Sales Amount	Break Date
1	xxx	1400	1/5/2018
2	YYY	2100	1/4/2018

[scotly-victor]

May be this

Table:

Load *,

if("Sales Amount New">=1000,'T','N') AS Flag;

Load * ,if(id=peek(id),rangesum("Sales Amount",Peek("Sales Amount New")),"Sales Amount") as "Sales Amount New";

Load * inline [

id, Name, Process Date ,Sales Amount

1, xxx, 1/1/2018, 200

1, xxx ,   1/2/2018, 200

1, xxx, 1/3/2018, 200

1, xxx, 1/4/2018, 200

1, xxx ,   1/5/2018,  200

1, xxx, 1/6/2018, 200

1, xxx, 1/7/2018, 200

2, YYY,    1/1/2018, 300

2, YYY, 1/2/2018, 300

2, YYY, 1/3/2018, 300

2, YYY, 1/4/2018, 300

2, YYY, 1/5/2018, 300

2, YYY, 1/6/2018, 300

2, YYY, 1/7/2018, 300 ];

Final_Table:

Load

id AS Result_id,

Name AS  Result_Name,

Sum("Sales Amount") as Result_Sales,

Date(min(if(Flag='T',"Process Date"))) as Result_Date Resident Table Group By id,Name;