That's a common mistake to make. The reason for this is ColorFunctionScaling. It remaps the range of values to run from 0 to 1 across the plot range. So basically, where Sin[x] is closest to -1, the filling is fully transparent, and where Sin[x] is closest to 1 it is fully opaque. The Abs here does not change anything.
First of all, you'll need to set ColorFunctionScaling -> False:
Plot[Sin[x], {x, 0, 6.0}, PlotStyle -> Red, PlotRange -> All,
ColorFunctionScaling -> False,
ColorFunction -> Function[{x, y}, Hue[1, 1, 1, Abs[y]]],
Filling -> Axis]
But now it is white (or, rather, transparent) only very close to zero as opacity kicks in quite fast. If you must have transparency, you can, for example, replace the alpha argument with 1 - Abs[y]^3 or Abs[y]^3 (depending, where you want red and where white):
But if you ask me, the simplest way is just ColorFunction -> (Blend[{Red, White}, Abs[#2]] &)
Plot[Sin[x], {x, 0, 6.0}, PlotStyle -> Red, PlotRange -> All,
ColorFunctionScaling -> False,
ColorFunction -> (Blend[{Red, White}, Abs[#2]] &), Filling -> Axis]
Note, that the filling here is white and not transparent. If transparency is necessary, you can try instead (Blend[{RGBColor[1, 0, 0, 1], RGBColor[1, 1, 1, 0]}, Abs[#2]] &)