topic Re: Date On Which an Employee Attained a Cumulative Sum Goal? in App Development

Date On Which an Employee Attained a Cumulative Sum Goal?

jdean1012 — Sat, 16 Nov 2024 01:53:58 GMT

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.

Re: Date On Which an Employee Attained a Cumulative Sum Goal?

Kushal_Chawda — Tue, 14 Jul 2020 21:37:11 GMT

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

Re: Date On Which an Employee Attained a Cumulative Sum Goal?

jdean1012 — Wed, 15 Jul 2020 14:43:11 GMT

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

Re: Date On Which an Employee Attained a Cumulative Sum Goal?

Kushal_Chawda — Wed, 15 Jul 2020 15:47:58 GMT

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;

Re: Date On Which an Employee Attained a Cumulative Sum Goal?

Charlotte_Carter — Sat, 28 Dec 2024 10:41:39 GMT

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.