0

Using User Input Control within a formula to filter data

Hi

 

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.

Thanks

2 comments

  • 0
    Avatar
    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

    @D:D

    @E-E

    @C:C

    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:

    SUM(

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

    -

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

    )/COUNT(

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

    )*100

     

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

    Thanks

    Meggan

  • 0
    Avatar
    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.