Creating Benchmarks from Previous Year

Comments

1 comment

  • Avatar
    Joseph Polefrone

    Please see edited version below:

     

    SELECT

    COUNT(m.[value]) AS [Volume]
    , m.[Date] AS [Date]

    --Temp for Testing
    ,(ToInt(COUNT(m.[value]) * 1.03)) AS [BenchmarkTestValue]

    --CASE Statement for Last Year Benchmark Percentage (increase 3%) ****STILL NOT WORKING****
    ,CASE WHEN m.[Date]= CreateDate((ToInt(GetYear(m.[Date])-1)),GetMonth(m.[Date]),GetDay(m.[Date]))
    THEN (ToInt(COUNT(m.[value]) * 1.03))
    ELSE Null END AS [Benchmark Target]


    FROM [Main] m
    WHERE m.[Date] <= Now()
    AND m.[Date] >= CreateDate((ToInt(GetYear(Now())-1)),GetMonth(Now()),GetDay(Now()))
    GROUP BY m.[Date]

    0
    Comment actions Permalink

Please sign in to leave a comment.