query.sql

SELECT 
    DATE_FORMAT(date_value, '{{ groupby }}') AS date_value,
    SUM(values) AS sum
FROM
    table
GROUP BY
    date_value

report.json

VARIABLE: {
    name: "groupby",
    display: "Aggregate by",
    type: "select",
    options: [
        {display: "Day", value: "%Y-%m-%d"}, 
        {display: "Calendar Week", value: "%Y CW %u"},
        {display: "Month", value: "%Y %M"}],
    default: "Calendar Week"
}