Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
jdean1012
Contributor III
Contributor III

Date On Which an Employee Attained a Cumulative Sum Goal?

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.

Labels (4)
3 Replies
Kushal_Chawda

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

jdean1012
Contributor III
Contributor III
Author

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.

DateEmployeeVendor Amount  Running Employee/Vendor Total 5K_Flag
1/1/2020JeffMicrosoft $       500.00 $                                    500.00 
1/1/2020JeffMicrosoft $       250.00 $                                    750.00 
2/15/2020JeffMicrosoft $     1,250.00 $                                 2,000.00 
3/20/2020JeffMicrosoft $       400.00 $                                 2,400.00 
3/31/2020JeffMicrosoft $       500.00 $                                 2,900.00 
4/13/2020JeffMicrosoft $       750.00 $                                 3,650.00 
5/1/2020JeffMicrosoft $     1,500.00 $                                 5,150.00X
5/5/2020JeffMicrosoft $         50.00 $                                 5,200.00X
1/1/2020MattOracle $     3,000.00 $                                 3,000.00 
2/3/2020MattOracle $     3,000.00 $                                 6,000.00X
3/1/2020MattOracle $     3,000.00 $                                 9,000.00X
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;
​