Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Can you share sample data to try on?
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 |
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;