Is there a way to use SUM with Rules, such as IF
Sorry for the wall of text, I am attempting to articulate fully what my inquiry is, and due to information privacy, I am using sudo names as place holders.
I am attempting to generate a summary module.
I am using 2 independant heirchies, with one of the line items/fields in the first heirarchy matches a value in the second heirarchy. The second heirarchy is summing their count based off of the field in the first heirarchy.
I am able to complete an overall summation:
X.Count[SUM:Y.Value to segregate by]
Works as intended. My test cases counts properly under the right segregating value and gives the correct total count value(s).
I now want to take using the same segregation and perferably the same count line item, but I want to limit the summed values further by line item, for example(s):
Jim Bob makes change requests. He has made a total of 100 different requests in the last 2 months, his boss has approved 40, and 30 of them are for Red Cars.
Use Case: I would like to know by submitters name how many requests had been - submitted, denied, approved, disputed, completed, are Red, etc.(list formated options).
Results: I have that Jim Bob submitted 100 total changes from my above sum. He submitted 30 change requests for Red Cars.
How do I find: that it was 30 for Red Cars? How many were set to 'submitted'? (different count values/lines items in the summary module)
Use Case: I would like to know based on a boolean if their leaders' are giving approval (boolean format).
Results: Jim Bobs boss approved 40, only 5 of them were for Red Cars.
How do I find: the number of approvals for Jim Bob? That only 5 where Red Cars?
Use Case: I would be picking two dates or time periods (date format) and outputting all of the above totals by grouping.
Results: Jim Bob made 50 requests between Monday the 22nd and Friday the 26th. He made 25 after that (today is the 30th). The other 25 requests he made before the beginning of this month, but during last month.
How do I find: that he put in his requests last week? this month? last month? between selected dates?
Notes/Issues:
I am attempting to use as few new lines as possible, due to model size.
I already have a count line item for each entry, as well as all necessary column values to identify each factor.
Eg. Line items: Bosses approval, Submitter (Jim Bob), The date of submission, Car Color, Count (always equal to 1), etc.
I have selectors that are able to choose end date and start date, that interact as upper bounds and lower bounds for the date value to fit into.
TLDR; I want to essentially complete the SUM function with IF statements to generate counts based on different line items, that are not in the same heirarchy but share a common value (line item entry/value and list item). I need to do this without adding additional lines if at all possible.
I am aware I can create a count field for each sum value that I am attempting to gather. Eg. I can assign a line item specific to each list value and complete the sum based off of that new line item. I am attempting to avoid doing this.
Thank you in advance.
Best Answer
-
Hi @obriegr,
Happy to give this a shot, but also TLDR, your summary is pretty much correct that you would need 2 line items for most of these use cases, one to filter down the numbers and then a second to SUM on that new line item. I’ll try to break out some alternative solutions to consider as well though.
First use case: The ideal way to solve this is to add the Requester as another dimension to the result module. Then you would add a second SUM at the end of your original aggregation formula (let me know if you want more details here). But as you mentioned space/sparsity is a concern, you should be careful to only apply this to the exact subset of requesters that you’re trying to measure. Same thing with the Red Cars example, add Color (or whatever the dimension is) to the result module and formula as well.
Second use case: This one requires a second number line item with an IF condition as you mentioned. IF Approved THEN Red Car Count ELSE 0. Not sure if there are any other workarounds for this, sorry.
Third use case: You could add a time dimension to the result module, and add the time period to SUM by to your aggregation formula. Same issue to watch out for space/sparsity issues, but this is a pretty standard alternative option.
Hope that helps!
1
