Some interesting comments above regarding the difference between the S and the Q4, although some of them do seem to be more orientated towards what the commentator drives, human nature I suppose, who would want to admit they may not have the "best" model
You need to refer to the press comments regarding the S from when it was launched if you want an unbiased opinion, almost unanimously the motoring press stated that the S was the car the Brera should have been all along, not the heavy ponderous beast it was.
Whilst mine is only a 2.2 I found that on a recent track day it could easily stay on the heels of the Q4 cars around the twisty bits (Pirelli P Zero's), but obviously lost out on the straight, and would in the wet too but, on normal roads you would not drive like that anyway. Whether I could do that because the 2.2 S is quite a bit lighter I do not know but, for chuckability, steering feel, lightness and responsivness, the S beats the lardy and sluggish standard Brera easily as a drivers car. The very reason I never brought a Brera when they first came out.
One of our section memebrs has a Q4 Brera and he rates it as the most lumpen Alfa he has ever driven apart from the 1 day in the last 12 months when we had snow down here. He is stunned at how my S drives.
In summary then, only you know what you want out of the vehicle, so I guess you need to decide! An extra 100kg of 4wd drive train makes a difference to handling and consumption as well as the loading on the brakes if you go on the track with it, and consumption is pretty appalling anyway but, in the wet and snow the Q4 is very secure. The S is lighter on its feet, does not stress it's brakes as much and it's a bit nipper with very slightly better fuel consumption, plus a very nice cabin, and lowered with some pretty wheels and stuff but no Blue and Me if that's important. Second hand the prices even out between the two vehicles except at a few dealers although new there was a bit of a difference. Both can be upgraded, the AD supercharger package seems to be the one if your serious and have a round £6k to spend so, its all up to what you want the vehicle for and how much you want to modify it.
As Squadrone Rosso will be able to attest to after speaking to Peter Cambridge, there are very good reasons Prodrive never kept the 4wd system and why they plumped for the Pirelli's. Chatting to your mate down the pub or on a forum is all well and good but ultimately, who knows more, them or the professionals.
Best of luck and I am sure whatever you chose you will still enjoy it because after all, it's an Alfa