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]

If you want to compare the monthly totals across years, you would be better off setting up the columns with Group Type as #1 and month as #2, where {@month} is based on a formula:

month({table.date})

Then for year, add two summaries, which use conditional formulas like this:

//{@CurrYear}:
if year({table.date})={?CurrentYear} then
{table.amount}

//{@PrevYear}:
if year({table.date}) = {?CurrentYear}-1 then
{table.amount}

Then add a third summary {@0} based on this formula:

whilereadingrecords;
0

This will act as a holder for the variance.

Then select {@CurrYear}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curryr := currentfieldvalue;
false

Then select {@PrevYear}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar prevyr := currentfieldvalue;
false

Then select {@0}->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar curryr;
numbervar prevyr;
totext(curryr-prevyr,2);//2 for two decimals

You can go into the customize style tab->summary fields and choose horizontal and "show labels". Change the labels as necessary.

-LB

 

[sfabry]

First, thank you for taking the time to answer my question...and now I have more of my own: (I apologize if I am being dense!)
1)I should have mentioned that my columns were set up with month as a formula (same as yours) then date sorted on year, then group type as if-then-else.
But by setting up the columns with group first and then month, it gives a total for only that group type for the date range. They would like the month + group type compared (variance of case count) by year - I dont think putting the group first can accomplish that, or am i being short-sighted?
2)Your suggestion for year looks like it includes a parameter? I do have a parameter set for date range - do I need to set this up to select year instead?
3)Is table.amount referring to my distinct count?
4)You mention setting them as summaries - to me that means placing them in the "summarized fields" section. I would think you would want to put them in the column section?
I was trying to post what it looks like but unfortunately I dont have access to dropbox here to post the file for you to see.

 

[lbass]

Right now your crosstab probably looks like this (where group type = A and B):

Jan Feb
2011 2012 2011 2012
A B Tot A B Tot A B Tot A B Tot
Svc1 1 2 3 2 4 6 3 5 8 4 5 9
Svc2 2 4 6 1 4 5 2 2 4 3 1 4
Svc3 1 5 6 4 4 8 6 2 8 1 4 5

So what is it you want to compare? And where would you show the comparison?

-LB

 

[sfabry]

yes, exactly. they would like to compare jan 11 total and jan 12 total, ETC. -
Jan Feb
2011 2012 VAR 2011 2012 VAR
A B Tot A B Tot A B Tot A B Tot
Svc1 1 2 3 2 4 6 3 5 8 4 5 9
Svc2 2 4 6 1 4 5 2 2 4 3 1 4
Svc3 1 5 6 4 4 8 6 2 8 1 4 5

 

[lbass]

What version of CR are you using?

-LB

 

[sfabry]

sorry, i had meant to include that in my original post - XI release 2

 

[lbass]

How many total row instances are there likely to be? Would the crosstab extend beyond one page vertically?

-LB

 

[sfabry]

probably not vertically but does horizontally. there are 14 rows now and it is unlikely that there will be many additions in the foreseeable future.
I am also hoping to export into excel eventually - which does work fine for now - but want to get this to work regardless of the export.
thank you again for yor time LB

 

[lbass]

Are they always the same 14 row instances? There are two approaches, one which hard codes and the other that is a little more complex, but is dynamic.

-LB

 

[sfabry]

I would say 95% of the time it will be the same. But if they want to apply this report to other sites it could change/have additions - or if they only want to look at certain ones out of the group....so planning ahead, id rather have to ability for it to change

 

[lbass]

Try my solution in: thread767-1674420. You should be able to do this without the changes the original poster made.

-LB

 

[sfabry]

OK -I am having trouble getting this to work but i think it could be where i am placing it. Below if what it currently looks like. To clarify, I dont care about the grand total and would like the variance between years per month.
Jan Feb GT
2011 2012 Tot2 2011 2012 Tot2
A B Tot1 A B Tot1 A B Tot1 A B Tot1
Svc1 1 2 3 2 4 6 3 5 8 4 5 9
Svc2 2 4 6 1 4 5 2 2 4 3 1 4
Svc3 1 5 6 4 4 8 6 2 8 1 4 5

this is what i have in the TOT1 column (i just started with 12 for now):

whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar curr1;
numbervar prev1 := curr1;
numbervar curr2;
numbervar prev2 := curr2;
numbervar curr3;
numbervar prev3 := curr3;
numbervar curr4;
numbervar prev4 := curr4;
numbervar curr5;
numbervar prev5 := curr5;
numbervar curr6;
numbervar prev6 := curr6;
numbervar curr7;
numbervar prev7 := curr7;
numbervar curr8;
numbervar prev8 := curr8;
numbervar curr9;
numbervar prev9 := curr9;
numbervar curr10;
numbervar prev10 := curr10;
numbervar curr11;
numbervar prev11 := curr11;
numbervar curr12;
numbervar prev12 := curr12;
if cnt=1 then
curr1 := currentfieldvalue;
if cnt=2 then
curr2 := currentfieldvalue;
if cnt=3 then
curr3 := currentfieldvalue;
if cnt=4 then
curr4 := currentfieldvalue;
if cnt=5 then
curr5 := currentfieldvalue;
if cnt=6 then
curr6 := currentfieldvalue;
if cnt=7 then
curr7:= currentfieldvalue;
if cnt=8 then
curr8 := currentfieldvalue;
if cnt=9 then
curr9 := currentfieldvalue;
if cnt=10 then
curr10 := currentfieldvalue;
if cnt=11 then
curr11 := currentfieldvalue;
if cnt=12 then
curr12 := currentfieldvalue;
false

this is what i have in the TOT 2:
whileprintingrecords;
numbervar cnt := 0;
true

but it is actually being supressed. are the variables supposed to be cnt=country, curr=current? I am just trying to translate it, so to speak...

 

[lbass]

curr=current, cnt = count, prev = previous. Just my shorthand.

The formula you are showing belongs in the suppress formula area for Tot1, so if that's where you put it, then that is correct. The reset formula belongs in a grand total row (made up of column totals, and appearing at the top of the crosstab) in the same cell ( Total). You are not showing that, so go into the customize style tab->uncheck "suppress column totals".

Please also look at the sample crosstab that I attached to the thread. You can look in the formatting->suppression->formula areas of the cells to find the correct formula locations.

-LB

 

[sfabry]

Ok - I am looking at the sample report and, I am sorry if I am being dense, the grand totals are supressed in customize style and the formulas are entered in different spots. There are also no comparisons/there are no variances. In other words, its completely different than what I thought I was supposed to be doing?
Currently in my report, I do not have grand totals suppressed and have the reset formula there but it is still being suppressed. I tried unchecking suppress (in format field) and leaving the formula in but it has no effect. The TOT1 is not being suppressed, even with the box checked (b/c of the false?).
The only things checked in customize style are "repeat row labels, keep columns together, column totals on top, show cell margins."
FYI I also increased the count to 18 and the crosstab is in the report header.

 

[lbass]

Okay, I think this is too complex, and you should try a different approach. Are you always comparing the current year to the previous year? Or at least only two years? Are there always the same two group types?

-LB

 

[sfabry]

i will email myself the reports and post them for you at home since i am unable to upload anything here. then i think it will be easier to communicate what i am trying to accomplish.
you have the patience of a saint!

 

[sfabry]

hopefully this works.

 

[lbass]

Here is a mockup of your report using differences between the current year and previous year. I saw that you started to do the same thing with YTD, but I didn't go there in this example. Please study the formatting formulas (suppression and display string), and notice the setup within the crosstab expert which replaces column fields with separate summary formulas.

http://www.mediafire.com/?08fxnmj785h57l8

-LB

 

[sfabry]

whew where to start...trying to tackle this today.
most of the formulas you used make sense but in the following @I/P LastYear:

if {@Pt Status}="InPatient" and
{@Year}=year(currentdate)-1 then
{@Case Record No} else
tonumber({@null})

I am not clear on:

{@Case Record No} else
tonumber({@null})

maybe its just the field names tripping me up?
Also, what is the difference between putting the formulas in the SUPRESS section or the DISPLAY section for calculations like this?
Can you tell me what makes the new columns appear?
last thing, do I need to set the date up the way you did and not use a parameter?
I will try to replicate what you did and make this work with my fields...
thank you again!