Totals (across a list) without a parent member

I have a module that is dimensionalized by row ID (#1-#n), no parent member for that list. It is an input template to model new expenses. What I am aiming to do is to have another line item (could be in a different module if needed) that will look to see if any of the rows have data.

Essentially each row has line items that need to be input. Once all input then that row will be "Ready." I want to summarize so the user knows if the entire sheet is "Ready."

How do I go about that without a parent member?

Thank you!

Accepted answers

adpinto

Thank you for the replies. The module that is dimensionalized by Row_ID is also dimensionalized by Users. I created a separate module dimensionalized by users and summed across users.

This was more of an issue of how to capture any of the records being filled out not checking each row for all the fields populated - got that part. Just needed a count and separate module.

All comments

andrewtye

What I'd probably do is have a line item at the end that checks whether all previous are empty or not (presumably every cell needs to be filled) give that a 1 if complete.

Then have a top-line summary item but only do sum on that final line item.

Otherwise you'll need to have a further line item that's list formatted and then have another module dimensioned by said list and then sum the line item that has the 1 or 0 check on it.

Hope that helps!

kevin.cho

Agree with this - only change I'd make is that rather than loading 1 or 0 (if value populated/not populated), I'd just use a boolean formatted line item.

For more explicit instructions:

Create your individual check line item, dimensioned by "Row ID".
1. Name = Individual Check
2. Formula = Value <> 0 AND ISNOTBLANK(Value 2) (etc, etc, whatever check you have)
create a list called "Helper" with one item in it (called Dummy).
Create a line item, dimensioned by "Row ID" (your input module dimension), and formatted as Helper.
1. Formula = "Helper.Dummy"
2. Name = "Row ID Helper"
Create a line item, dimensioned by nothing, formatted as Helper.
1. Formula = "Helper.Dummy"
2. Name = "Helper Lookup"
(Option 1) Create your summary check line item, dimensioned by "Helper".
1. Name = Summary Check
2. Set formula = Individual Check[ANY: Row ID Helper]
  1. Result will be the ANY aggregation of the Individual Check line
3. For use in the rest of your model, you can do "Summary Check[LOOKUP: Helper Lookup]
  1. This just gets rid of the Helper dimension
(Option 2) Create your summary check line item, dimensioned by nothing
1. Name = Summary Check
2. Formula = Individual Check[ANY: Row ID Helper, LOOKUP: Helper Lookup]
  1. Only difference here is combining the ANY and LOOKUP, technically not best practice but for small dimensions/calcs, I haven't found a discernible performance impact.

einas.ibrahim

Hello Adam @adpinto

Both answers given are good answers. If I understood your ask correctly, you need to know if the whole "form" has been completed or not, not necessarily the number of completed items. In this case, going with a boolean and using the ANY (or ALL) functions depending on your case, would be the better solution.

adpinto

Thank you for the replies. The module that is dimensionalized by Row_ID is also dimensionalized by Users. I created a separate module dimensionalized by users and summed across users.

This was more of an issue of how to capture any of the records being filled out not checking each row for all the fields populated - got that part. Just needed a count and separate module.

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…