Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
Partner - Creator II
Partner - Creator II

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
Digvijay_Singh

Can you share sample data to try on?

anindya_manna
Partner - Creator II
Partner - Creator II
Author

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

scotly-victor
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;