Try something like this :
tmp:
LOAD
[Month],
[code],
[brand],
SUM([SUnits]) AS [SUnits],
SUM([Values]) AS [Values],
SUM([Units]) AS [Units]
FROM dbo.Table
GROUP BY [Month], [code], [brand];
tmp2:
LOAD *,
Num(Left([Month], 4)) AS [YEAR],
If(Right([Month], 3) = 'Jan', 1,
If(Right([Month], 3) = 'Feb', 2,
If(Right([Month], 3) = 'Mar', 3,
If(Right([Month], 3) = 'Apr', 4,
If(Right([Month], 3) = 'May', 5,
If(Right([Month], 3) = 'Jun', 6,
If(Right([Month], 3) = 'Jul', 7,
If(Right([Month], 3) = 'Aug', 8,
If(Right([Month], 3) = 'Sep', 9,
If(Right([Month], 3) = 'Oct', 10,
If(Right([Month], 3) = 'Nov', 11, 12))))))))))) AS [MONTH]
RESIDENT tmp;
tmp3:
LOAD t.*,
(SELECT SUM([SUnits])
FROM tmp2 t2
WHERE t.code = t2.code AND t.brand = t2.brand
AND t.YEAR = t2.YEAR AND t.MONTH >= t2.MONTH) as SUnits_YTD
RESIDENT tmp2;
tmp4:
LOAD t.*,
(SELECT TOP 1 SUnits_YTD
FROM tmp3 t2
WHERE t.code = t2.code AND t.brand = t2.brand AND t.MONTH = t2.MONTH AND t.YEAR = (t2.YEAR+1)) AS SUnits_LYTD
RESIDENT tmp3;
tmp5:
LOAD t.*,
RANK() OVER (PARTITION BY code, Month ORDER BY (SUnits_YTD-SUnits_LYTD)/SUnits_LYTD DESC) AS Rank_growthSUnits
RESIDENT tmp4
WHERE NOT ISNULL(SUnits_LYTD) OR SUnits_LYTD <> 0;
FINAL_RESULT:
LOAD
Month,
code,
brand,
SUnits,
Values,
Units,
SUnits_YTD,
SUnits_LYTD,
Rank_growthSUnits
RESIDENT tmp5;
Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.
