Filtering pivot table issue

[Lolo]

Hello,

I have a strange problem with filtering in a pivot table. Let me explain :

In the attached file I have 2 columns with numeric values.
HOWEVER, blank value is a possible value (and zero too). So because of this, column is considered as text in the pivot table (I can see if I want to sort the columns, it displays 'sort A to Z').

When I filter the second columns QUOTA PCT BALANCE greater than 0,5 for example, lines filtered are the good one. so blank values are well managed.

however, when I filter 1st column, QUOTA_BALANCE greater than 50, the result is not the good one ! Seems it makes a text comparison and the value 200 is not shown for example ('2' of 200 <5), only values 9 an -7 are shown...

I can understand that mix text values and numeric values is not a good practice, but for some columns it works fine! In my real pivot table, I have several columns and only QUOTA_BALANCE columns which have the problem !!?? Perhaps because of negative values only possible in this column ?

So I have 2 questions :
* Have you an idea on why in the 1st case the filtering is valid, and not in the second one ?
* How can I fix my problem ? I cannot convert blank value in 0 value, because the zero value is not the same thing. I have tried #N/A, but the behavior is the same as '' value. I have tried to remove negative values, same behavior.

thank you for your help...

 

[Luke M]

Bad news and good news. Bad news is, both columns are actually filtering the wrong way. It just doesn't show up when you filter decimals, because decimals numbers naturally read from decimal place to the right, to determine larger/smaller. I only discovered problem when I record a macro of filtering, to see what XL thinks is going on.

'Percentage column
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "QUOTA_PCT_BALANCE").PivotFilters.Add Type:=xlCaptionIsGreaterThan, Value1 _
        :=".5" '<-- BAD NEWS
'Regular column
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "QUOTA_PCT_BALANCE").ClearAllFilters
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "QUOTA_BALANCE").PivotFilters.Add Type:=xlCaptionIsGreaterThan, Value1:= _
        "50" '<-- BAD NEWS

those quatation marks around the numbers means that XL is applying a string filter, which is what you suspected.

Good news is, that XL was perfectly content to run this macro

Sub test()
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "QUOTA_BALANCE").PivotFilters.Add Type:=xlCaptionIsGreaterThan, Value1:= _
        50 '<-- GOOD NEWS
End Sub

In the latter, it's filtering based on a number.

Granted, it doesn't make a lot of sense why XL can do both of these. I'm guessing that PivotTables use strings for the Label filters by default.

So, the work-around is to filter the columns using a macro.

 

[Lolo]

Thank you for these info. Interesting.

However, I notice that some blank values was not really blank... Probably due to the source of data (SQL export). At the beginning the data was not really correct (soemtimes null, sometimes '', sometimes ' ', and I also replace in the pivot table, the value (blank) --> ' ' !)

I have forced the empty value by VBA (cell.clearcontents) for all "blank" (or blank like) cells, and now it is OK. See updated file in attached.

but I don't understand why are you saying it is not OK about QUOTA_PCT_BALANCE column. filtering seems ok. for example, If I filter "lesser than 0",2 for example, lines displayed are the good ones.

 

[Luke M]

The filter on PCT column does display the correct results, but it's not following a number filter logic, it's following a string logic. It just works because all the numbers in that column will start with "0."

Think from a string perspective...these numbers:
0.12345
0.10000
0.1
0.2345

If we filter by number for everything less than "0.15", then a string filter checks first character, then 2nd character, then third. Because of what a decimal represents, where each number has decreasing place value, and in our set, they are start at same place value (the ones digit), then the filter works.

However, if you had numbers
0.12345
12.345
2.345

and filtered (again, in string mode) for everything greater than 2, it would only show 2.345, as the first character of 2nd number is a 1, which XL says is less than 2 (again, in string mode). I know it's a bit confusing.

 

[Lolo]

No no it is clear,and your explanation logic, conform to a string filtering indeed.
If it follows a string logic, in all cases, Since the value in PCT column is between 0 and 1 with same number of chars, it should be ok.

But have you an idea on why in my new "fixed" test file when i filter on column balance(1st column), now it works fine, and follows à numéros Logic as far as i can see ? while the column is considered as text ?!
It becomes tricky to understand !

 

[Luke M]

But have you an idea on why in my new "fixed" test file when i filter on column balance(1st column), now it works fine, and follows à numéros Logic as far as i can see ? while the column is considered as text ?!

Ah, I think this also helps answer my own questions as well. It appears that XL uses a type of logic like this about a data column:
Column contains all numbers? Treat as numbers
Column contains all numbers and some blank cells? Treat as numbers
Column contains numbers and some cells with space/text? Treat as strings

Thus, it was that last logic line that was screwing things up. Before, when there were some non-empty blank cells, the PT was treating everything as a string. Now that there's just numbers and blanks, it's able to know that yes, we're just dealing with numbers.

In short, as you said at beginning, best if we have no blank cells. They just seem to cause all sorts of headaches! But, I know they can't always be avoided.

 

[Lolo]

Yes clear, And it is fine that Excel do this
Now, I just have to be sure that my initial blank values (from SQL) are really blank (an not ' ' or ''), and it will works fine.

Thank you a lot for your help.

 

[Lolo]

But just another remark.
What is complex (al least at the begining of my analysis ), is that when the pivot table was refreshed 1st time , I explicitly replaced "(blank)" (-->(vide) in my french version) data by a space char (so all (blank) values of the column becomes ' ').
I suppose it is at pivot table level, so excel is not confused with original data type (blank + numbers).