Qlik Community

Ask a Question

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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 (3)
3 Replies
MVP
MVP

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

Contributor III
Contributor III

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
MVP
MVP

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