how to write the formula

[Picture008]

Hi
I have two columns one is like Account number and other one is like creation date.
so I need to create formula to find out last month newly added accounts and YTD formula ie I need to find out total accounts added from Jan 1st to this current month.
in the report the columns like newly_added_accounts and YTD_accounts. this report is running every month 1st and it shoul capture the records for the last mont and records from 1st January to till date.

Thank you very much.

 

[Picture008]

HI
I am using CR10 and Oracle database 9.2
Plese give provide me correct logic on which way it is going to work

Thank you,

 

[BobSuruncle]

I'm trying to interpret what you're asking for.
It sounds like you want a count of New Accounts Year-to-Date and a count of New Accounts for the last full month.

Your Record Selection formula should be:

... Whatever your record selection needs ...
AND {YourTable.YourDateField} in YearToDate

Create a formula called NullString, and don't enter ANY text for it. Just create it and save it.


Your Year To Date Formula should be:

If {YourTable.YourDateField} in YearToDate 
then {YourTable.YourAccountField}
else {@NullString}

Your Month To Date Formula should be:

If {YourTable.YourDateField} in LastFullMonth 
then {YourTable.YourAccountField}
else {@NullString}

Place these 2 formulas in the Details section of your report.
Then, create a summary on them, using the Distinct Count summarization method.

This should give you the number of new account for each of the 2 periods.


Bob Suruncle

 

[Picture008]

Hi Bob,

I Got it .

Thank you very much.