Removing empty Numbered List items from Module with Time & Version dimensions
I want to be able to create a "cleaning" dashboard that removes redundant line items from a numbered list.
I have a module that has the numbered list in question with dimensions TIME and VERSION, which acts as my data hub and records all the information for reports on the system.
My first attempt to build this view was to have the list in a new module, line items for all the versions pointing back at the data hub with formula: [SELECT: TIME.All Periods, SELECT: VERSIONS.Actual] ("actual" swapped for each of the different versions).
I then had a line item "If is Blank" that is boolean formatted and that has the formula :IF Actual = 0 AND Budget = 0 AND Original Budget = 0 AND 'Budget Bid 19/20' = 0 AND 'Q1 Forecast' = 0 AND 'Q2 Forecast' = 0 AND 'Q3 Forecast' = 0 THEN TRUE ELSE FALSE.
This would work if it was not for the effect of totalling the line items against all time periods - eg. if I had a +100 in Apr and a -100 in Mar, the line would be ticked because it sums to zero, even though there was data against it - I do not want to remove lines that have ANY data against them, even if they net to zero!!!
I already have a delete function that can pick up the boolean list, I just need to get the right items ticked!
You're on the right lines
What you'll need to do is create a new boolean line item at the lowest level in the souce to check for values <>0 and set the summary option to ANY
That way the total will be true for anything not zero, so your delete flag can check for FALSE or NOT
I would also say that for the check you don't actually need to do IF xx = 0 then TRUE ELSE FALSE, you can just check for xx = 0 and the answer will always be either TRUE or FALSE - just a best practices tip for formula efficiency!
I hope that helps
Try it in this way!!
Step 1: Add a line item "To find 0" and make it as boolean formatted and set the summary as "All" and put the formula =0 of the source line item that you are refering. In my ex. Value is my source line item with all the list applied as you mentioned.
Step 2: Add a line item "Delete" and it should be boolean formatted with only numbered list as dimension because we are trying to remove items from this list. Then write a formula as mentioned in the screen shot. Now you will be able to see only the items which are needs to be deleted and it is irrespective of total as "0" but it is dependent on non-zero inputs.
So if you check the item 1 entry in above screen shot we have +30 ,-15 and -15 but still it is not checked as per your requirement.
Let me know if you have any concerns!!.
Have a good day.
Thank you @DavidSmith !
Once implemented it didn't quite work but that was because my formula had AND instead of OR!
Have a great day.
This is very clearly laid out and will help people in the future, thank you Kavin!1