cancel
Showing results for 
Search instead for 
Did you mean: 

converting date to dayNumber

Former Member
0 Kudos

Post Author: kalyan

CA Forum: Formula

Hi i have a typical problm .. , i have a column with receiveddate from which i should show some count of recods on that date

here is the requirement

The Day 0 should be u2018todayu2019s dateu2019 and items appearing in that row should be for items with a received date of u2018todayu2019. The Day 1 should be u2018yesterdayu2019s dateu2019 and items appearing in that row should be for items with a received date of u2018yesterdayu2019. This should go to Day 16+.

i should show this in reports like this

DayNum ReceivedDatre recCount

Day 0 08-30-2007 -- 5

Day 1 08-29-2007 -- 15

Day 2 08-28-2007 --10

like this

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Post Author: SKodidine

CA Forum: Formula

Easy enough. This solution assumes you have reccount for each of the 16 days otherwise it will skip that day.

1. In your record selection criteria type {table.receiveddate} in (currentdate - 16) to currentdate;

2. create a group on {table.receiveddate} and click on OPTIONS and in the COMMON tab, choose 'in descending order' and under 'The section will be printed:', choose 'for each day'.

3. Suppress Group Header and the Details Section.

4. Create a formula with: 'Day ' & totext((groupnumber - 1),0,'','');

5. Place the formula in the group footer and then place the group name next to it.

6. Create a running total that counts 'reccount' for each record and resets on change of group. Choose the group you created above.

7. Place the running total in the group footer next to the formula.

8. Create appropriate headings.

That's it!