Cross Tab Expert - Formula to split summerized fields between columns

[TanyaHarp]

Trying to determine number of days people stayed in bed and breakfast by quarter. If one person stayed 14 days and there was 2 days in one quarter and 12 in next quarter then the days need to be split up.

The following formula right now will put the days into the proper quarter and shows which of those stays need to be split. Problem is I don't know how to split those days into different quarters.
(First Quarter starts March 1, not Jan 1)


WhilePrintingRecords;
Local NumberVar NumDays := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
Local NumberVar DayOfMonth := Day({DAYS_STAYED_VIEW.START_DATE});
Local NumberVar TotalOfDays:= DayOfMonth + NumDays;
Local DateVar firstOfMonth := Date(Year({DAYS_STAYED_VIEW.START_DATE}),Month({DAYS_STAYED_VIEW.START_DATE}),1);
Local DateVar lastOfMonth := Date(DateAdd("d",-1,DateAdd("m",1,firstOfMonth)));
Local NumberVar LastDayOfMonth := Day(lastOfMonth);


if Month({DAYS_STAYED_VIEW.START_DATE}) in 4 to 6 then
( if (TotalOfDays > LastDayOfMonth) then
'Another Quarter'
else
' First Quarter')

else if Month({DAYS_STAYED_VIEW.START_DATE}) in 7 to 9 then
( if (TotalOfDays > LastDayOfMonth) then
'Another Quarter2'
else
' Second Quarter')

else if Month({DAYS_STAYED_VIEW.START_DATE}) in 10 to 12 then
( if (TotalOfDays > LastDayOfMonth) then
'Another Quarter3'
else
' Third Quarter')

else if Month({DAYS_STAYED_VIEW.START_DATE}) in 1 to 3 then
( if (TotalOfDays > LastDayOfMonth) then
'Another Quarter4'
else
'Fourth Quarter')

 

[TanyaHarp]

Forgot to mention I'm running Crystal Reports 2008

Thanks

 

[lbass]

Your formula doesn't seem to reflect a March 1 start, but instead, an April 1 start to the first quarter. I also don't see how this translates correctly to quarters. And I am unclear on what you see as the remaining step.

-LB

 

[TanyaHarp]

Sorry yes,it should read April 1 - June 30 is Quarter 1, July 1 - Sept 30 is Quarter 2, Oct 1 - Dec 31 is Quarter 3 and Jan 1 - March 31 is Quarter 4.

The remaining step is how can I divide the length of stay between multiple quarters.

Examples
Guest 1 first night was June 2 and she stayed for 8 days
- Quarter one has 8 days added to it.
Guest 2 first night was June 28 and he stayed for 10 days
- Quarter one has 2 days added to it
- Quarter two has 8 days added to it

Cross tab - Column is @Quarters (above formula), Rows is Room_Types and Summerized field is length_of_stay

I'm trying to figure out how to split guest 2's ten days over the two different quarters

 

[lbass]

So what is the problem you have with your current formula? I actually do not see how this results in assignment to the correct quarter--what do the values "Another Quarter N" refer to? Is this your actual formula?

-LB

 

[TanyaHarp]

Another Quarter N refers to those stays that spans more than one quarter. When it falls into one of these quarters I need to split the length of stay between the two quarters that the stay spanned. Right now it just throws the total number into Another Quarter N rather than Quarter 1 = 2 days and Quarter 2 = 8 days.

 

[TanyaHarp]

can I do something like:

if Month({DAYS_STAYED_VIEW.START_DATE}) in 4 to 6 then
( if (TotalOfDays > LastDayOfMonth) then
First Quarter.length_Of_stay = 2;
Second Quarter.length_of_stay = 8;
else
' First Quarter')

 

[lbass]

Right now, it isn't working properly as it is only testing by month, not quarter.

Create these formulas:

//{@qtrst}:
datevar start := {DAYS_STAYED_VIEW.START_DATE};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
numbervar qtrst := 0;
if datepart("q",start)= 1 then
qtrst := 4 else
qtrst := datepart("q",start)-1;

//{@qtrend}:
datevar start := {DAYS_STAYED_VIEW.START_DATE};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
datevar end := start + days-1;
numbervar qtrend := 0;
if datepart("q",end)=1 then
qtrend := 4 else
qtrend := datepart("q",end)-1;
qtrend

//{@dayscurrqtr}:
datevar start := {DAYS_STAYED_VIEW.START_DATE};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
datevar end := start + days-1;
numbervar dyscurrqtr := 0;
if datepart("q",start) = datepart("q", end) then
dyscurrqtr := days else
if datepart("q",start) <> datepart("q", end) then
dyscurrqtr := dateserial(year(start),month(start)+1, 1)-start;

//{@daysnextqtr}:
datevar start := {DAYS_STAYED_VIEW.START_DATE};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
datevar end := start + days-1;
numbervar dysnextqtr := 0;
if datepart("q",start) <> datepart("q", end) then
dysnextqtr := end-(dateserial(year(start),month(start)+1,1)-1);

//{@Qtr1}:
if {@qtrst} = 1 then
{@dayscurrqtr} else
if {@qtrend} = 1 then
{@daysnextqtr}

//{@Qtr2}:
if {@qtrst} = 2 then
{@dayscurrqtr} else
if {@qtrend} = 2 then
{@daysnextqtr}

Repeat to create remaining two quarters.

Then insert a crosstab and add the four QtrN formulas as summaries and in the customize style tab, check horizontal display and show labels for the summaries.

If you want to show total days, add your duration field as a fifth summary. Add your room types field as the row field.

-LB

 

[TanyaHarp]

Looks good, two things I should have mentioned. The year for the first three quarters must be equal to a parameter year (ex 2011 entered) and the last quarter would be the parameter year plus 1 (2012). This way only stays from 2011-2012 fiscal year would show up (not previous/future years).

Also, what happens if Guest 3 stays for 96 - 365 days (so it would span three or four quarters)

 

[lbass]

Yes, you should have.

For fiscal year 2012 (April 1, 2011 to March 31, 2012), you can create a formula like this:

//{@FY}:
year({table.date}+275)

Then use a record selection formula:

{@FY} = {?FiscalYear}

I'm sorry I can't spend more time on your newly added scenario where someone stays for more than two quarters. I already spent a lot of time on this. You can just expand the above logic.

-LB

 

[TanyaHarp]

ok, I made two more formulas called qtrmid1 and qtrmid2 which test to see if there are 1 or 2 quarters in the middle of the first and last quarter of a stay. So now i'm trying to get the @Qtr2 to do this:

if {@qtrst} = 2 then
{@dayscurrqtr}
else if {@qtrend} = 2 then
{@daysnextqtr}
else if {@qtrmid1} = true then
{@Qtr3}={@daysofqtr}
if {@qtrmid2} = true then
{@Qtr4}={@daysofqtr}

Am I able to set @Qtr3 and 4 from @Qtr2?

(@daysofquater is a formula that determines the number of total days in a specific quarter)

 

[TanyaHarp]

Oh and I changed @daysnextqtr to figure out what the last qtr days are

//{@daysnextqtr}:
datevar start := {DAYS_STAYED_VIEW.START_DATE};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
datevar end := start + days-1;
numbervar dysnextqtr := 0;


if datepart("q", end) - datepart("q",start) = 1 then
dysnextqtr := {DAYS_STAYED_VIEW.LENGTH_OF_STAY} - {@dayscurrqtr}
else if datepart("q", end) - datepart("q",start) = 2 then
dysnextqtr := {DAYS_STAYED_VIEW.LENGTH_OF_STAY} - {@dayscurrqtr} - {@totaldaysonemidqtr} //last formula is total days of one quarter
else if datepart("q", end) - datepart("q",start) = 3 then
dysnextqtr := {DAYS_STAYED_VIEW.LENGTH_OF_STAY} - {@dayscurrqtr} - {@totaldaystwomidqtr} // last formula is total days of two quarters
else if datepart("q", end) - datepart("q",start) = -1 then
dysnextqtr := 0;

 

[TanyaHarp]

@dayscurrqtr isn't working out properly.

Stay begin May 1, 2011
Stay length 142 days
Stay end date Sept 19, 2011

@dayscurrqtr is coming out at 31 days when it should be 50 or 51 days.

 

[lbass]

That's because these formulas were built to account only for stays that fell within a two-quarter period.

-LB

 

[TanyaHarp]

Thanks LB, I think I almost have it worked out and will post it when/if I'm done. The above example is a two-quarter period, my mistake though for not being more clear in the original post. the rest of the changes I'm making for it being up to a four quarter stay is dependent on the daycurrqtr formula. I have a weakness when it comes to calculating dates.

 

[TanyaHarp]

This is the changes I made to @dayscurrqtr. Correct me if I am wrong because like I said I'm not strong with date calculations. I'm now working on the mid quarter calculations for multiple quarter visits.

@dayscurrqtr

datevar start := {DAYS_STAYED_VIEW.START};
numbervar days := {DAYS_STAYED_VIEW.LENGTH_OF_STAY};
datevar end := start + days-1;
numbervar dyscurrqtr := 0;
if datepart("q",start) = datepart("q", end) then
dyscurrqtr := days else
if datepart("q",start) <> datepart("q", end) then
(if month(start) in [1, 10] then
dyscurrqtr := dateserial(year(start),month(start)+2, 31)-start else
if month(start) in [4, 7]then
dyscurrqtr := dateserial(year(start),month(start)+2, 30)-start else
if month(start) in [2, 11] then
dyscurrqtr := dateserial(year(start),month(start)+1, 31)-start else
if month(start) in [5, 8] then
dyscurrqtr := dateserial(year(start),month(start)+1, 30)-start else
if month(start) in [6, 9] then
dyscurrqtr := dateserial(year(start),month(start), 30)-start else
if month(start) in [3, 12] then
dyscurrqtr := dateserial(year(start),month(start), 31)-start
)