Why is the App Store not automatically showing the update for macOS Sierra?
Update (Oct 3, 2016)

Apple will now automatically download the upgrade for macOS Sierra on computers running OS X 10.11 El Capitan. You will see it automatically download as long as you have enough disk space, and have Automatic Updates enabled. 

You must still specifically run the installer to upgrade to macOS Sierra.



Original Answer

macOS Sierra is not an update. It is an upgrade. 

In macOS/OS X, upgrades are not automatically offered to you through the Update tab of the Mac App Store, nor the Automatic Updates functionality. You will not see behaviour like Microsoft offering Windows 10. You must specifically download the installer (and run it) to upgrade to macOS Sierra.

Either click on its entry in the Mac App Store (currently in the Featured tab), or by opening this direct link.