Yikes, Grouping on a month only if there is a value?! 1

[DeviousDi]

HI,

I'm currently transferring excel report to crystal, and have come across the following formula, that needs to be created in crystal, with the "English" version underneath!!! I don't have a problem with creating the formula, what I have a problem with is that this formula carries as on if there are values in the fields.

Line 1: = IF ( FF7 - EF7 < 0 , FF7 / EF7 * $EF$6,
= IF ( DSO Debt Value – DSO Revenue Value < 0, DSO Debt Value / DSO Revenue Value * 30 (Apr), ($EF$6 will be the number of days in the current month)

Line 2: IF ( FF7 - EF7 - ED7 < 0 , ( FF7 - EF7 ) / ED7 * $ED$6 + $EF$6,
IF (DSO Debt Value – DSO Revenue Value – Mar DSO Revenue Value < 0, (DSO Debt Value - DSO Revenue Value) / Mar DSO Revenue Value * 31 (Mar) + 30 (Apr),

Line 3: IF ( FF7 - EF7 - ED7 - EB7 < 0 , ( FF7 - EF7 - ED7 ) / EB7 * $EB$6 + $ED$6 + $EF$6,
IF (DSO Debt Value – DSO Revenue Value – Mar DSO Revenue Value – Feb DSO Revenue Value < 0, (DSO Debt Value - DSO Revenue Value – Mar DSO Revenue Value) / Feb DSO Revenue Value * 28 (Feb) + 31 (Mar) + 30 (Apr),

Line 4: IF ( FF7 - EF7 - ED7 - EB7 - DZ7 < 0, ( FF7 - EF7 - ED7 - EB7 ) / DZ7 * $DZ$6 + $EB$6 + $ED$6 + $EF$6,
IF (DSO Debt Value – DSO Revenue Value – Mar DSO Revenue Value – Feb DSO Revenue Value – Jan DSO Revenue Value < 0, (DSO Debt Value - DSO Revenue Value – Mar DSO Revenue Value – Feb DSO Revenue Value) / Jan DSO Revenue Value * 31 (Jan) + 28 (Feb) + 31 (Mar) + 30 (Apr),

Line 5: IF ( FF7 - EF7 - ED7 - EB7 - DZ7 - DX7 < 0 , ( FF7 - EF7 - ED7 - EB7 - DZ7 ) / DX7 * $DX$6 + $DZ$6 + $EB$6 + $ED$6 + $EF$6)))))
IF (DSO Debt Value – DSO Revenue Value – Mar DSO Revenue Value – Feb DSO Revenue Value – Jan DSO Revenue Value – Dec DSO Revenue Value < 0, (DSO Debt Value - DSO Revenue Value – Mar DSO Revenue Value – Feb DSO Revenue Value – Jan DSO Revenue Value) / Dec DSO Revenue Value * 31 (Dec) + 31 (Jan) + 28 (Feb) + 31 (Mar) + 30 (Apr)))))

How can I get this formula to recur for as many months as is needed, without having to type the formula out?

Thanks for any help!

Di

 

[hilfy]

Did you ever get an answer for this? I have some thoughts if you haven't.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

Hi Hilfy,

Never did get any answers, from here or anywhere else!

Any ideas would be much appreciated.

Thanks

Di

 

[hilfy]

If I understand your issue correctly, you don't want to create a different formula for every month due to the need to get the number of days in the current month and the number of days in the previous month. Is that correct?

If so, I would create two formulas - one to get the days in this month, one to get the days in last month. The formulas would look something like this:

{@DaysThisMonth}
Switch(
Month(CurrentDate) in [1, 3, 5, 7, 8, 10, 12], 31,
Month(CurrentDate) in [4, 6, 9, 11], 30,
Month(CurrentDate) = 2, Date(Year(CurrentDate), 3, 1) - Date(Year(CurrentDate), 2, 1)
)

{@DaysLastMonth}
Switch(
Month(CurrentDate) in [1, 2, 4, 6, 8, 9, 11], 31,
Month(CurrentDate) in [5, 7, 10, 12], 30,
Month(CurrentDate) = 3, Date(Year(CurrentDate), 3, 1) - Date(Year(CurrentDate), 2, 1)
)

Both of these formulas calculate the actual number of days in February because the number of days will change depending on the year. "Switch" is like a short version of a long "If..Then..Else If...Then...." statement.

You would then use these formulas whenever you need to get the number of days in a month.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Madawc]

Another method is to get the first of the next month, then use DateAdd to subtract a day.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[DeviousDi]

Hi,

Posting back as I thought I might have over come this little hurdle, but alas not!

The months have to go as far back as there is an outstanding invoice, so I won't actually know how many months there are. Could be a couple of months, could be 18!!!

Is there any way of recurring a formula until the last month with an open or unpaid invoice is met?

Thanks

Di