Removing empty Numbered List items from Module with Time & Version dimensions

CallumWright

Hi all,

 

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!

 

Many thanks,

 

Callum

DavidSmith

@CallumWright 

Hi

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

David