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

CallumWright
CallumWright
in Security and tenant administration

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

Tagged:

Best Answers

  • DavidSmith
    DavidSmith
    Answer ✓

    @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

  • kavinkumar
    kavinkumar
    Answer ✓

    HI Callum,

     

    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.Removing empty Numbered List items from Module with Time & Version dimensions.PNG

    Removing empty Numbered List items from Module with Time & Version dimensions2.PNG

     

     

    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.

     

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

    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.

     

    Thanks,

    Kavin.

Comments

Categories