Finding the last date of the quarter

[jcl5]

I'm using this formula to calculate my quarters:-

Local NumberVar Quarter := DatePart("q",{HIST.HIST_DATE});
If Quarter = 1 then Quarter := 4 else Quarter := Quarter -1;
"Q"&ToText(Quarter,0);

but I need to create a total to count interactions in each quarter where the company is less than two years old at the end of each quarter ({COMS.STAR_DATE})

I was going to use datediff but can't work out how to calculate last date of each quarter.

Thanks

jcl5

 

[lbass]

You could try this formula {@lastdateinqtr} for the last date in the quarter:

Local NumberVar Quarter := DatePart("q",{table.date});
If Quarter = 1 then Quarter := 4 else Quarter := Quarter -1;

if quarter = 4 then dateadd("m",1, date(year({table.date}),3,01))-1 else
dateadd("m",1, date(year({table.date}), (quarter+1)*3,01))-1;

Then use {@lastdateinqtr} in the datediff formula.

-LB

 

[Reebo99]

Change your formula to

Global NumberVar Quarter := DatePart("q",{HIST.HIST_DATE});
If Quarter = 1 then Quarter := 4 else Quarter := Quarter -1;
"Q"&ToText(Quarter,0);

Then you could use the following in a separate formula to determine the last date in a quarter:

Global NumberVar Quarter;
If Quarter = 1 then Maximum(Calendar2ndQtr) else
If Quarter = 2 then Maximum(Calendar3rdQtr) else
If Quarter = 3 then Maximum(Calendar4thQtr) else
If Quarter = 4 then Maximum(Calendar1stQtr)

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.