Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anindya_manna
Contributor 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
Honored Contributor III

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

Can you share sample data to try on?

anindya_manna
Contributor II

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

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
Contributor

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

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;

Community Browser