Changing Cross Tab Columns to Column A, Column B etc..

[SundancerKid]

Hi Everyone,

I am creating a report that was previously done through excel. I know that there is a easier way.

I am using 15 months of data. I am using a Start Date and End Date to control how much data I need.

I want to create a cross tab on each month Gross Margin Data.

I need to compare the data in groups of 3 starting with earilest to current.

Example:

200304 200305 200306 200307 200308 200309 200310 200311 200312 200401 200402 200403 200404 200405 200406

I want to start with the first period, and I want it to be named Column [1], Column [2], Column [3] etc.. all the way to Column [15].

I am rating the columns in groups of 3 so i would add Column 1 thru Column 3 to a Fomulated Column known as Column [E], Column 4 thru Column 6 as Column [D] etc.. all the way to Column 13 thru 15 as Column [A] I will be doing more formulas on the Columns A thru E to base a percentage on a Sales Bonus for employees.

My Question is: How do I rename the Period Columns?

Thanks

Rick

 

[synapsevampire]

Why do you need an end date if you're always pulling 15 months?

Use:

{table.date} in {?startdate} to dateadd("m",14,{?startdate})

To name the periods, use:

If {table.date} >= {?Start Date}
and
{table.date} <= dateadd("m",1,{?Start Date})
then
"Column 1"
else
If {table.date} >= dateadd("m",1,{?Start Date}
and
{table.date} <= dateadd("m",2,{?Start Date})
then
"Column 2"
etc...

You lost me on the "I need to compare the data in groups of 3 " and then you show 15 months that aren't readliy grouped by 3's. Hopefully the above will help you to handle that requirements as well.

-k

 

[SundancerKid]

How this work for the 15 months within different years?