variance column in crosstab 1

[sfabry]

I am trying to add a variance column to my crosstab, I did see some other forums but it is not exactly what I am looking for. Ive also seen some suggestions for the advanced calculations option but i do not have that available and would also like to avoid a manual crosstab...
I am new/self taught so please be gentle!
My current crosstab is setup as (column): month, year, group type, (row): group service and the summary is a distinct count of case record numbers. I am hoping to compare the monthly totals in each year - so the variance between total for jan 11 and jan 12, etc. Ive tried experimenting with a few things but have not have much luck. Any help or tips would be appreciated!

 

[lbass]

I used formulas to show you where to put your fields of the same name, as I didn't have access to your tables.

You can replace currentdate with a parmeter date.

{@null} is a new formula that you open and close without entering anything.

The {@0} formula is used to create the holder for the variance column. Relabel.

The suppression area is used to setup the variables used to create the comparison. The display string area is for displaying the results.

-LB

 

[sfabry]

what i meant about the formula is that it is saying "if its an INPATIENT and the year is equal to last year then ? else null" - what is the @case record no (order.id) for? and what does it make null?

 

[lbass]

It doesn't make anything null. You should replace {@case record no} with your case record no field. The null clause is the default value, and makes the default null instead of 0. This allows you to insert distinct count or count summaries on the formula and get correct results. Otherwise you could get the default value counting as 1 for all records that don't meet your conditional critera.

-LB

 

[sfabry]

ahha, thank you

 

[sfabry]

i think i have actually gotten this to work! i did adjust your YEAR formula becuase it was reversing THIS and LAST year:

if year({v_Surg_Proc_CR.cr_prdate})=2012 then
year (currentdate)-1 else
year(currentdate)

i changed to:

if year({v_Surg_Proc_CR.cr_prdate})=2012 then
year (currentdate) else
year(currentdate)-1

THANK YOU!!!!!