Month Previous Year Compared to Month Current Year

[beth4530]

I need to compare month last fiscal year to current month and fiscal year.
I don't want to count the months for last year that are less than the Last full month for the current fiscal year.
So I don't want a count for April, May, June of 2012.

How can I modify this count formula?

if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= LastFullMonth



then 1 else 0

 

[IanWaterman]

Assuming you have not filtered out last years data in select expert

@currentmonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= LastFullMonth

@pymonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} >= dateadd("yyyy", -1, minimum(LastFullMonth)) and
{Patient_Clin_Tran.proc_chron} <= dateadd("yyyy", -1, maximum(LastFullMonth))

Ian

 

[beth4530]

Hi Ian- Thanks for your help,

Here is an example of the cross tab I'm working with. I don't want the totals to include the months from last year beyond the LASTFULLMONTH to get an accurate comparison of the fiscal years to date.
Not sure how to apply your formulas.....
Last Yr Count - This Yr Count
JUL 208 - 296
AUG 244 - 301
SEP 199 - 288
OCT 302 - 305
NOV 311 - 309
DEC 300 - 312
JAN 299 - 301
FEB 283 - 333
MAR 304 - 366
APR 205-
MAY 322-
JUN 297-
TOT 3274 - 2811

 

[IanWaterman]

Modify formula to return 1 or 0 then sum these in Cross Tab

@currentmonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= maximum(LastFullMonth)
then 1 else 0

@pymonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= dateadd("yyyy", -1, maximum(LastFullMonth))
then 1 else 0
Ian

 

[IanWaterman]

sorry formulae not quite right, forgot to restrict to year

@currentmonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= maximum(LastFullMonth) and
{Patient_Clin_Tran.proc_chron} <= date(year(currentdate), 1, 1)

then 1 else 0

@pymonth
if {Patient_Clin_Tran.status}in ["CO","SH"] and
{Patient_Clin_Tran.proc_chron} <= dateadd("yyyy", -1, maximum(LastFullMonth))and
{Patient_Clin_Tran.proc_chron} <= date(year(currentdate)-1, 1, 1)
then 1 else 0

Ian

 

[beth4530]

Thank you Ian!