This is an accepted solution.
If you have Shopify Plus you can use the Launchpad App to schedule sales on specific collections. If you're working on any other plan though, the way I would typically set up a sale would be to create a collection called "Sale" that imports products automatically that have a "sale" tag added. Then I'd export my products to a CSV that I want included in the sale, add ", sale" to the tags column, copy my variant prices over to to the variant compare at price column, then do a calculation in the price field to give you the percentage off. Say you wanted to give 20% off, I'd make the variant price column:
= CompPrice * .8
then copy that formula down the column and format the number.
When I wanted to start the sale I'd upload that CSV and overwrite the products, then when that sale was over I'd reupload the original csv and overwrite the products again.
If you don't have Shopify plus, (or you do.. ) you can use the this app to create an automated Sale collection based on a simple condition such as discount % or even more advanced such as "discount > average discount" and more.
Give it a try and see if it gives a solution