Using User Input Control within a formula to filter data



I using a formula to allow me to work out the the NPS score, which is SUM(@D:D-@E-E)/COUNT(@C:C)*100   (D= Promoter, E=Detractor, C=Net Promoter).   This all works perfectly.   However,  when I try to use the User Input Control to filter the subregion,  it fails to work.  

The user input control works for all my other Klips,  just not the NPS one, as this is the only one that is based around a formula.


I have tried SUMIF, SELECT, GROUP, etc,  but nothing seems to work.  


Any help would be greatly appreciated.



  • 0
    Meggan King

    Hi Richard,

    Thanks for posting! This is a common issue that happens to people - filtering in formulas. There are a few ways to solve this. You can create hidden data for each column of your formula, plus hidden data for the filter on your user input control 

    Hidden Data




    Subregion - Add a filter to this using your user input control variable 


    Then your formula would be

     SUM(&D:D-&E-E)/COUNT(&C:C)*100  > Where you use a reference to the Hidden data instead of pointing to the data in your data source


    The alternative is do build out  a more complex formula, I'll show a mock up over multiple lines:


    SELECT(@D:D, subregion=variable),


    SELECT(@E:E, subregion=variable),


    SELECT(@C:C, subregion=variable),



    Give these a try and let us know if you run into issues!



  • 0
    Richard Powell

    Thanks Meggan


    Your first option of all the hidden data worked a charm.   I had only been adding the SubRegion as hidden data previously.


    Many thanks, I now have a little hair left ;-)

Please sign in to leave a comment.