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;
​

[Charlotte_Carter]

To help track when an employee/vendor combination hits a cumulative charge goal like $5000, you can use SQL queries with SUM() and GROUP BY, combined with the running total approach. A simple way to do it is by grouping the charges by employee/vendor and month, then calculating the running total. If you're looking for a credit card solution for your business, check out  Getmyoffer CapitalOne for CapitalOne Rewards and exclusive CapitalOne Credit Offers. They offer pre-approval tools and easy CapitalOne Application processes to help you access the best card options.