Issue when filtering data

[jackersantos]

I created a calculated field to provide a % of actual hours versus budgeted. Now I'm trying to filter everything that is below 50% but I'm facing some issues. (Some percentages have a large number such as 33.33333333%) When I use the filter tool It brings values that are higher than 50% even though I have specified the opposite. How can I select only the values less than 50% ?

[cmcclellan]

Sorry, but based on your post I have questions and no answers 😞 

Are you talking about a text field or numeric field ?

What does your filter look like ?

From my point of view, a percentage should be a numeric, where 50% = 0.5 and 100% = 1

Your filter is then X > 0.5

When your users want to see 50% with the sign, it's because you've formatted the number like that.

Does that help ?

[DiegoParker · Accepted solution]

Hi @jackersantos,

The reason why this is happening is because your field is formatted as a string. Hence, when you are using your filter, instead of filtering every number under 50%(0.5) what Alteryx is doing is filter any text that on a dictionary order appears before "50%".

Imagine we have a dictionary, all the words with A will show up before any word with B. Here is the same, all the words starting with 0,1,2,3,4 will show up before any word starting with 5, hence why 2500% is showing under. Does that make sense?

To solve your problem you will have to get rid of the percentage symbol and divide your number by 100. You can do this with the following formula: 

ToNumber((Replace([%], "%", "")))/100 keep in mind that then to filter you will have to use 0.5 instead of 50.

Find the workbook attached.

Hope this helps! if does, can I ask you to mark it as solution so other users can refer to it? Thanks! if you have any further questions do not hesitate on asking.

Best,

Diego

[jackersantos]

I was mistaken. I was formatting the field as % and the reason why filtering was not working is what Diego said. I changed the values to number (valuex/valuey= equals a number instead of %) and now filter worked. Thanks!