Number of rows summed up by grouping them, each group has a different number of rows
You seem to need rolling sums over windows of 4 rows - so there need to be some correction for the first 3 rows of each group inside every partition:

with cte as
(   select 
        RetailerID, Category, WeekId, value,
        row_number() over
            (partition by RetailerID, Category
             order by WeekId)
            as rn,
        count(*) over
            (partition by RetailerID, Category)
            as tc,  -- total count per partition
        sum(value) over
            (partition by RetailerID, Category
             order by WeekId
             rows between 3 preceding
                      and current row)
            as sv
    from temptable 
    where Weekid between 1 and 12 
      and RetailerID = 34
)
select
    RetailerID, Category, WeekId, value,  
    case when rn &lt;= floor(tc / 4) * 4
        then case (rn % 4)
                 when 1 then lead(sv, 3) over w
                 when 2 then lead(sv, 2) over w
                 when 3 then lead(sv, 1) over w
                 else sv
             end
        else sv
    end as sum_value
from
    cte
window w as 
    (partition by RetailerID, Category order by WeekId) ;


Test at dbfiddle.uk. 

Works in MySQL 8 and SQL Server (2012+).