I’ve been trying to solve my problem for days consulting old posts about this subject, but I’m probably loosing something back.
My goal is obtain the total value of each worker monthly and put it on a list on the screen.
Note that there are different values for each Working Hour and each worker appears in several rows with their respective values in the All Income column.
I have already created a multiple Relation from User to User; I created a User ID (worker) Template with the date (month and year); finally I tried a Rollup through this Relation, but the SUM option does not appear, only Count and Count Unique.
Plenty of ways to approach this, can you describe in a little more detail your desired user experience, and how you would want the data presented?
On which screen would the data be presented? On each users details screen, or some generic screen where you could select from a list of users and months?
Would you just be viewing one user at a time, or would you want to view all data for all users for a selected month? (or conversely, all data for a single user for all months?)
Create a single row Helper Table, and add a RowID column, plus two User Specific text columns; one to hold a UserID (or email), and one to hold the Month selection.
Sit a screen on top of that table and add two choice components - one for the User selection and one for the Month selection.
– For the month selection, you can probably make use of those two columns you already created (Y, M). Maybe create a template from those and use as the choice source. There are better ways to do this, but you may as well make use of what you already have.
Add a Query column to the Helper Table. Target it at the data table and apply the following filters:
– User is This row->User
– Month is This row->Month
Add a Rollup->Sum that targets the Query column.
I would do this in the User Profile table:
Create a Math column that returns the Date of the first day of the current month: Now - Day(Now) + 1
Add a Query column that targets your data table, and apply the following filters:
– User is This row → User
– Date is on or after This row → 1st of month (the previous math column)
If you trace backwards through the steps, where does it fall apart? Does the query have matching rows? If you trace backwards, what is the first step that doesn’t have the expected results?
As Jeff said, the way to debug is to trace backwards.
If you show a couple of screen shots, we may be able to pinpoint the failure point pretty quickly.
