Pivot table sorting numbers as text

[crookesa]

I have a pivot table created from a table on a different worksheet.
My first row is a field called Working Week followed by 2 other fields.

I am trying to sort the Working Week columns numerically from lowest to highest but the pivot table sees the data as text and is not sorting as I would expect.

The column in the table is a formula based value =IF([@Date]<>"",IF(WEEKNUM([@Date],15)=53,1,WEEKNUM([@Date],15)),"") and works as expected as I have set the category to number.

Is there anyway to get the pivot table to sort correctly, I have the same setup in another workbook and it works as expected but can't for the life of me figure out why it's not sorting correctly.

Pivot Table Sort

Source Data

 

[GraH]

Sorting is not required it seems as shown below.
Did you sort on the correct pivot field?

No sort:

Sort on Weeknum

 

[crookesa]

Thanks for the help, I tried creating it again using my formula and it worked. Not sure why it saw it as text even though I specified it was a Number type.

 

[GraH]

Good to read you got it sorted and that somehow my comment was useful for you.
You have double quotes in your formula evaluation, note that is a textstring for Excel. Empty it is, but also text.

 

[crookesa]

Good to read you got it sorted and that somehow my comment was useful for you.
You have double quotes in your formula evaluation, note that is a textstring for Excel. Empty it is, but also text.

Ok, my intention of the formula was that if that the @Date field in the table was left blank it wouldn't show the value 1 but present a blank. Would there be a better solution then for error catching and presentation of the calculated field?

 

[GraH]

Since it is working now, you probably don't need to change anything.
Alternative, is wrapping your formula inside a TEXT() function, like TEXT(if-formula, "00"). Sorting on this text field will work.