INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Crystal 2013 formula based on calendar months

Crystal 2013 formula based on calendar months

(OP)
formula needed to display a count of records by date for a prior month than print month that is grouped by calendar month with prior 3 months data for the last 12 months.

example

if print date is 8/8/16 report should show
Aug15 Sep15 Oct15 Nov15 Dec15 Jan16 Feb16 Mar16 Apr16 May16 Jun16 Jul16

15 18 17 16 14 15 12 20 23 15 19 20

each column should include a count of the prior 3 months. So Aug15 should include count from June - Aug 2015, etc.

RE: Crystal 2013 formula based on calendar months

I am guessing that you might need 12 running totals that only evaluate on datediff(m, -1 (or -2, -3, etc.), current) and from that date diff to a date diff -3 (i.e, -4, -5, etc.). This is an abstract idea that I have not worked a proper formula. If I have time, I will see if I can work up something. Of course the report would need to be filtered for the past 15 months.

RE: Crystal 2013 formula based on calendar months

It would be very easy to do vertical instead of horizontal.

Formula @GroupMon
totext({table.date},"yyyyMM")

Group on that formula.
Create a Running Total to count or sum whatever your data is, and do not give it a reset.

RE: Crystal 2013 formula based on calendar months

(OP)
Vertical works but how to I get each grouped month info to total the current month and the prior two months?

So I want Jan 2016 to include a count of Nov, Dec and Jan
Feb 2016 to include Dec, Jan and Feb

RE: Crystal 2013 formula based on calendar months

Can you provide a bit more detail about the structure of the data. In your example in the original post, are the month's results straight from the database, or a count of transaction in those months? Are the months Calendar Months (so if run on 8/8/2016 August data represents just 8 days), or the month up to the date the report is run (data from 8 July to 8 August).


Cheers
Pete

RE: Crystal 2013 formula based on calendar months

(OP)
The months results are a count of transactions in the report in the month that I used the group by month feature. I want Calendar months so August would be the 8 days worth if I run it on the 8th.

smith 1/1/16
jones 1/12/16
count 2
dean 2/2/16
count 1
gile 3/4/16
Johnson 3/16/16
count 2 3 month count 5
davis 4/1/16
fields 4/2/16
brown 4 16/16
count 3 3 month count 6

RE: Crystal 2013 formula based on calendar months

OK, I achieved what you want this way:

Create the following 2 formulas, and insert them into the Group Footer for the Date Group:

CODE --> {@var_SET}

WhilePrintingRecords;
Global NumberVar M3;
Global NumberVar M2;
Global NumberVar M1;
M3 := M2;
M2 := M1;
M1 := Count ({Table.Date}, {Table.Date}, "monthly"); 

CODE --> {@var_DISP}

WhilePrintingRecords;

Global NumberVar M3;
Global NumberVar M2;
Global NumberVar M1;

M3 + M2 + M1 

The first of the 2 formulas will show the count for that month. The second formula will give the total for the 3 previous months.

Hope this helps.

Cheers
Pete

RE: Crystal 2013 formula based on calendar months

(OP)
I tired the formula above and I am having two issues:

Some customers that I am counting by month don't have counts every month, this is counting the last three month groups even if they are not consecutive months
Also for a customer that only has 2 months of data it is picking up the previous customers last months.

Example:

ABC CO
6/2016 13 13
7/2016 17 30
8/2016 26 56
9/2016 74 117

XYZ Co
1/2016 2 119
3/2016 4 80
4/2016 5 11

I have two groups, the first one is customer and the second one is my datefield that I am counting using the group by month option.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close