Totals (across a list) without a parent member

adpinto

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!

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.

 

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:

1. 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)
2. create a list called "Helper" with one item in it (called Dummy).
3. 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"
4. Create a line item, dimensioned by nothing, formatted as Helper. 
   1. Formula = "Helper.Dummy"
   2. Name = "Helper Lookup"
5. (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
6. (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.