Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

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?

3 Replies
Highlighted
Master III
Master III

Can you share sample data to try on?

Highlighted
Partner
Partner

dNameProcess DateSales Amount
1xxx1/1/2018200
1xxx1/2/2018200
1xxx1/3/2018200
1xxx1/4/2018200
1xxx1/5/2018200
1xxx1/6/2018200
1xxx1/7/2018200
2YYY1/1/2018300
2YYY1/2/2018300
2YYY1/3/2018300
2YYY1/4/2018300
2YYY1/5/2018300
2YYY1/6/2018300
2YYY1/7/2018300

I want to display this

  

IdNameSales AmountBreak Date
1xxx14001/5/2018
2YYY2100

1/4/2018

Creator II
Creator II

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;