Crystal 2013 formula based on calendar months 3

[jchewsmith]

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.

 

[kray4660]

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.

 

[Charliy]

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.

 

[jchewsmith]

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

 

[pmax9999]

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

 

[jchewsmith]

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

 

[pmax9999]

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]

[Code {@var_DISP}]
WhilePrintingRecords;

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

M3 + M2 + M1
[/Code]

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

 

[jchewsmith]

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.