Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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;
​