SSRS expression for current year's month divided by same months from last year

Question

SSRS expression for current year's month divided by same months from last year

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Jack,

I think this is close. Only Activity_Date is not in the dataset, just Month(Activity_Date) and YEAR(Activity_Date). It's somewhat of a cheat, but since the data is being partitioned by year, I don't think it matters. At any rate, this is what I came up with:

CREATE VIEW [dbo].[LeadsReferralsDelta]

AS

SELECT Division

, ActivityYear

, ActivityMonth

, MonthlyLeads

, LAG(MonthlyLeads,1) OVER (PARTITION BY Division, ActivityYear

ORDER BY ActivityYear, ActivityMonth) As PrevLeads

, MonthlyLeads - LAG(MonthlyLeads,1) OVER (PARTITION BY Division, ActivityYear

ORDER BY ActivityYear, ActivityMonth) As LeadsDelta

, MonthlyReferrals

, LAG(MonthlyReferrals,1) OVER (PARTITION BY Division, ActivityYear

ORDER BY ActivityYear, ActivityMonth) AS PrevReferrals

, MonthlyReferrals - LAG(MonthlyReferrals) OVER (PARTITION BY Division, ActivityYear

ORDER BY ActivityYear, ActivityMonth) AS ReferralsDelta

FROM MonthlySales;

GO

SELECT Division

, ActivityYear

, ActivityMonth

, MonthlyLeads

, YTDLeads

, PrevLeads

, LeadsDelta

, LeadsPercentChange

, LastYrMoLeads

, YOY_Delta

FROM

(SELECT Division

, ActivityYear

, ActivityMonth

, SUM(MonthlyLeads) OVER (PARTITION BY Division, ActivityYear

ORDER BY ActivityMonth

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) AS YTDLeads

, SUM(MonthlyLeads) OVER (PARTITION BY Division

ORDER BY ActivityYear, ActivityMonth

ROWS BETWEEN 12 PRECEDING

AND 12 PRECEDING) AS LastYrMoLeads

, MonthlyLeads

, 1.0 * MonthlyLeads/SUM(MonthlyLeads) OVER (PARTITION BY Division

ORDER BY ActivityYear, ActivityMonth

ROWS BETWEEN 12 PRECEDING

AND 12 PRECEDING) AS YOY_Delta

, PrevLeads

, LeadsDelta

, 1.0 * LeadsDelta/PrevLeads As LeadsPercentChange

, 1.0 * ReferralsDelta/PrevReferrals AS ReferralsPercentChange

FROM dbo.LeadsReferralsDelta) x;

The Referrals stuff is missing, but it's identical to the Leads calculations with a reference to the Referrals column instead. The YOY_Delta just looks back 12 rows... so it might be better using a Calendar table and outer joining to the totals stuff, just to be sure a missing row won't cause different months to be compared.

Turns out LAG was the way to go. (well, it's hard to tell to be sure... just not up to a proper performance test right now.)

Enjoy! Let me know if I missed something.

Pieter

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

Cool. Thanks for sharing the alternatives to an SSRS solution (SSRS forum, ahem).

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Not the fully exploded version (I used a couple of views to simplify the later queries a little)... but that's what I was thinking originally when I suggested that LAG() was the way to go. Only catch, AFAIK, is that you can't have missing values in your monthly summaries, because LAG goes back N records, not some number of months etc. (So you might need a Calendar table to force/guarantee the existence of all the month/year rows you need).

(1) If you're as smart as you seem to think you are, why didn't you come up with the solution yourself in the first place?

(2) do you have to have the last word every time?

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

Regarding LAG() you might have thought to ask if I was even using sql server 2012...which im not. Ours is still 2008r2. Ofcourse I came for help. Thanks.