Date On Which an Employee Attained a Cumulative Sum Goal?

[jdean1012]

Hey guys.  I have a ton of transactional credit card data.  I need to determine the Date on which a given Employee/Vendor combination achieved $5000 in charges using the load script.

For example, if Jeff charges $1000 to Microsoft on the first day of every month starting on January 1st, I'd want to know that he totaled $5000 with Microsoft on May 1st.

Any wisdom on this would be very much appreciated.

[Kushal_Chawda]

would you be able to share sample data with expected output?

[jdean1012]

Thanks for the reply!  Ultimately, the 5K_Flag column in this example is what I want to produce.  It is a flag that tells me that an Employee has reached $5K with a Vendor for the year, and flags every transaction after that point.

Date	Employee	Vendor	Amount	Running Employee/Vendor Total	5K_Flag
1/1/2020	Jeff	Microsoft	$       500.00	$                                    500.00
1/1/2020	Jeff	Microsoft	$       250.00	$                                    750.00
2/15/2020	Jeff	Microsoft	$     1,250.00	$                                 2,000.00
3/20/2020	Jeff	Microsoft	$       400.00	$                                 2,400.00
3/31/2020	Jeff	Microsoft	$       500.00	$                                 2,900.00
4/13/2020	Jeff	Microsoft	$       750.00	$                                 3,650.00
5/1/2020	Jeff	Microsoft	$     1,500.00	$                                 5,150.00	X
5/5/2020	Jeff	Microsoft	$         50.00	$                                 5,200.00	X
1/1/2020	Matt	Oracle	$     3,000.00	$                                 3,000.00
2/3/2020	Matt	Oracle	$     3,000.00	$                                 6,000.00	X
3/1/2020	Matt	Oracle	$     3,000.00	$                                 9,000.00	X

[Kushal_Chawda]

 below

Data:
LOAD
    "Date",
    Employee,
    Vendor,
    Amount,
    "Running Employee/Vendor Total"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
​
Final:
Load *,
     if(Running_Total>=5000,1,0) as [5K_Flag];
Load *,
     if(Peek(Employee)=Employee, RangeSum(Peek(Running_Total),Amount),Amount) as Running_Total
Resident Data
Order by Employee,"Date";
​
Drop Table Data;
​