How can I calculate change YearMonth over YearMonth for the past 4 years when Year and Month in a group in SSRS.
I need like that:
Row 2016 vs 2015 % Change is a division of January 2016 Premium/ January 2015 Premium and so on
Something like CASE WHEN year=2016 and month = 1 then 2016 premium/2015 premium I am trying to:
IIF(Fields!YearNum.Value=2016 and Fields!MonthNum.Value=1, Fields.Premium.Value/IIF(Fields!YearNum.Value=2015 and Fields!MonthNum.Value=1,Fields.Premium.Value,1),Nothing)
My query looks like that:
SELECT b.YearNum,
b.MonthNum,
b.MonthName,
SUM(Premium) as Premium,
ISNULL(sum(case when TransactionType IN ('Policy', 'Reinstatement') then 1 ELSE 0 END),0) as Bound,
FROM tblCalendar b
LEFT JOIN Test_Plaza_ProductionReport a ON b.MonthNum = MONTH(a.EffectiveDate) AND b.YearNum=YEAR(a.EffectiveDate)
WHERE YEAR(EffectiveDate) <> 2017
GROUP BY b.YearNum,
b.MonthNum,
b.MonthName
Aucun commentaire:
Enregistrer un commentaire