Syntax? Sum on Iif statement based on date?

[aellistechsupport]

I tried to do this from what I've gathered:

LastYear: Sum(Iif([Month]=DateAdd("yyyy",-1,DateSerial(Year(Date()),Month(Date()),[Totals],0))

I keep getting an error.

To always get prior year / last year's current month's sum of Totals.

This seems to works for current year and current month:

ThisYear: Sum(IIf([Month]=Month(Date()),[Month],[Totals]))

Full statement:

SELECT ComparisonSummary.SortOrder, ComparisonSummary.MemberGroup, ComparisonSummary.Type, Sum(IIf([Month]=Month(Date()),[Month],[Totals])) AS ThisYear, Sum(Iif([Month]=DateAdd("yyyy",-1,DateSerial(Year(Date()),Month(Date()),[Totals],0)) AS LastYear
FROM ComparisonSummary
GROUP BY ComparisonSummary.SortOrder, ComparisonSummary.MemberGroup, ComparisonSummary.Type;

How do I edit so that I'll always get the Sum([Totals] for this month last year?

[June7]

Need a year value in your data to base filtering on. I see only a [Month] field. Your expression for [ThisYear] will include records regardless of year.

BTW, Month, Year, Date are reserved words. Should avoid reserved words as names.

[aellistechsupport]

The "Month" is actually a date field.

That's true, I'll change it.

Ok, so how do I change that statement to also only Sum the Totals of current year and current reporting month?

Meaning it would be April 2014
And for LastYear it needs to sum the totals field for only April 2013.

This all have to handle when January comes to look at December and not confuse "LastYear" and "ThisYear" where they are accurately summing the correct year and month.

Where I am doing:

Between Year(Date())-1 And Year(Date())

To limit "last" year and "this" year. Not sure how to change it to handle come January as well.

[June7]

Best is to input year and month for filtering into controls on form and reference as parameter.

Sum(IIf([tbxYear] & [tbxMonth]=Format([datefield],"yyyym"), [Totals], 0) AS ThisYear

Sum(IIf([tbxYear]-1 & [tbxMonth]=Format(DateAdd("yyyy",-1,[datefield]),"yyyym"), [Totals], 0) AS LastYear

[aellistechsupport]

I think I've found an easier way to go about this?
since I'm always comparing last year and this year with this:

TRANSFORM Sum(Comparison.ReportingMonth) AS Totals
SELECT Comparison.SortOrder, Comparison.MemberGroup, Comparison.Type
FROM Comparison
WHERE (((Year([CreateDate])) Between Year(Date())-1 And Year(Date())) AND ((Month([CreateDate]))=Month(Date())))
GROUP BY Comparison.SortOrder, Comparison.MemberGroup, Comparison.Type
ORDER BY Comparison.SortOrder
PIVOT IIf(Year([CreateDate])=Year(Date()),"ThisYear","LastYear");

BUT need to change

 ((Month([CreateDate]))=Month(Date())))

to always look for current month with limit to year?
Current month for any year

[June7]

How do you want to handle the case of running report on February 1 morning but you really want January data? Hard coding current date will not allow adjustment.

[aellistechsupport]

It'll always run on the 7th of each month for the up to the prior month.

When January 7th comes it needs to process up to December 31st.

The parameter I have is a year ago to this year.

Will that work come next year, in Jan.?

Right now when I view it it shows upto end of April while in May

[aellistechsupport]

I tried this too and nothing:\

 DateAdd("yyyy",-1,Month(Date())) Or Month(Date())

[June7]

Then you don't want the current month, you want previous month?

Sum(IIf(Format([datefield],"yyyymm")=Format(DateAdd("m",-1,Date(),"yyyymm"), [Totals], 0) AS ThisYear

Sum(IIf(Format([datefield],"yyyymm")=Format(DateAdd("m",-13,Date()),"yyyymm"), [Totals], 0) AS Last year

Is CreateDate a full date/time value?

WHERE Format([CreateDate],"yyyymm") BETWEEN Format(DateAdd("m",-13,Date()),"yyyymm") AND Format(DateAdd("m",-1,Date()),"yyyymm")

[aellistechsupport]

Oh yea, duh!
No wonder, the data is April and I'm in May now.

CreateDate is a Date/Time field.


I think it works!


Thank you!

SELECT ComparisonDetails.SortOrder, ComparisonDetails.MemberGroup, ComparisonDetails.Type, Sum(IIf(Format([CreateDate],"yyyymm")=Format(DateAdd("m",-1,Date()),"yyyymm"),[Totals],0)) AS ThisYear, Sum(IIf(Format([CreateDate],"yyyymm")=Format(DateAdd("m",-13,Date()),"yyyymm"),[Totals],0)) AS LastYear, ComparisonDetails.CreateDate
FROM ComparisonDetails
GROUP BY ComparisonDetails.SortOrder, ComparisonDetails.MemberGroup, ComparisonDetails.Type, ComparisonDetails.CreateDate
HAVING ((Format([CreateDate],"yyyymm") Between Format(DateAdd("m",-13,Date()),"yyyymm") And Format(DateAdd("m",-1,Date()),"yyyymm")));

SortOrder	MemberGroup	Type	ThisYear	LastYear	CreateDate
1	Regular Member	First Family	31517	0	4/30/2014
2	Regular Member	Subsequent Family	15216	0	4/30/2014
3	Regular Member	Collegiate	949	0	4/30/2014
4	Regular Member	Introductory	4569	0	4/30/2014
5	Individual Memberships	Individual First Family	1536	0	4/30/2014
6	Individual Memberships	Individual Subsequent Fam	175	0	4/30/2014
7	Individual Memberships	Individual Collegiate	63	0	4/30/2014
8	Teams	Synchronized Skating Team	579	0	4/30/2014
9	Teams	Theater On Ice	100	0	4/30/2014
10	Basic Skills Member	Basic Skills	103877	0	4/30/2014
11	Friends of Figure Skating	Friend	146	0	4/30/2014
12	Friends of Figure Skating	Friend - Red Level	161	0	4/30/2014
13	Friends of Figure Skating	Friend - White Level	41	0	4/30/2014
14	Friends of Figure Skating	Friend - Blue Level	12	0	4/30/2014
15	Friends of Figure Skating	Friend - Pewter	4	0	4/30/2014
16	Friends of Figure Skating	Friend - Bronze Level	1	0	4/30/2014
17	Friends of Figure Skating	Friend - Silver Level	1	0	4/30/2014
18	Friends of Figure Skating	Friend - Gold Level	1	0	4/30/2014
19	Tests	In-House	0	134	4/30/2013
20	Tests	Online	0	20066	4/30/2013
21	Member Clubs	Regular	565	0	4/30/2014
22	Member Clubs	Regular Not Paid	0	626	4/30/2013
22	Member Clubs	Regular Not Paid	63	0	4/30/2014
23	Member Clubs	Collegiate Club	3	0	4/30/2014
24	Member Clubs	School Affiliated Club	66	0	4/30/2014
25	Basic Skills Programs	Active	0	2464	4/30/2013
25	Basic Skills Programs	Active	751	0	4/30/2014
26	Basic Skills Programs	Active w/ '0' mbs	0	1972	4/30/2013
26	Basic Skills Programs	Active w/ '0' Mbs	204	0	4/30/2014
27	Basic Skills Programs	Inactive	0	398	4/30/2013
27	Basic Skills Programs	Inactive	80	0	4/30/2014

[June7]

Wait a minute, not sure about the pivot clause. It still references the current date, not the previous month. This should cause issue in January because in January you really want the previous year as well:

PIVOT IIf(Year([CreateDate])=Year(DateAdd("m",-1,Date())),"ThisYear","LastYear");

[aellistechsupport]

Hi June7,
It didn't work.

Sum(IIf(Format([datefield],"yyyymm")=Format(DateAdd("m",-1,Date(),"yyyymm"), [Totals], 0) AS ThisYear 

Sum(IIf(Format([datefield],"yyyymm")=Format(DateAdd("m",-13,Date()),"yyyymm"), [Totals], 0) AS Last year

WHERE Format([CreateDate],"yyyymm") BETWEEN Format(DateAdd("m",-13,Date()),"yyyymm") AND Format(DateAdd("m",-1,Date()),"yyyymm")

It tried it today with your code and it's showing May's data with the April's data?

[aellistechsupport]

Was trying to do this now and I'm getting this year's:

LastYear: Sum(IIf(DateSerial(Year(Date())-1,Month(Date()),0),[Totals],0))

WHERE:

 CreatedDate = DateSerial(Year(Date()),Month(Date()),0) Or DateSerial(Year(Date())-1,Month(Date()),0)

Which does only give me last April and this April BUT not in the LastYear column.

While able to show Dec when Jan comes around.

[June7]

A little lost - what is state of the issue now? What works and what doesn't?

[aellistechsupport]

The statement you provided isn't working after all.
It is showing May's data.

It should only show last month's data for last year and this year