Excluding a month if not complete out of the Quarterly average section

[Hales008]

Hello Everyone,

I have a chart which displays a bar and Area. The Area chart is behind the bar chart. The bar chart shows Monthly Bid Values for a year - Jan05, Feb05, March05, Jan06, Feb06 etc... The Area chart shows the Quarterly average.

I have managed to create this, and all is OK, but I've been asked that if the month is not complete, then exclude from the Quarterly average calculation.

This is the first chart out of two. I do have another question, about another chart, but will post this separately.

Any help or feedback really appreciated.

Thanks,

Hayley

 

[lbass]

Use a record selection formula like:

{table.date} <= maximum(lastfullmonth)

-LB

 

[Hales008]

Hello,

Many thanks for your response. If I create a record selection formula, this will be for all records? Will it not? I don't want that to happen. The bar within the chart needs to show all months, but the area withing the chart only needs to show the quartley average if the month is complete. Could I put this piece of code in the formula field?

Thanks,

Hayley.

 

[lbass]

It would help to know how you have the chart set up--on change of field(s), show value field(s).

-LB

 

[Hales008]

Hi,

On the Select Expert I have 2005, 2006 and 2007 selected. I also have if status is not one of Cancelled/Postponed.


On the Chart I have Data - Advanced. On Change of, @YrQuarterConv - which is this formula:

Year ({All_Bids_for_Export.StartDate}) & "-" & Month ({All_Bids_for_Export.StartDate})

On the Show Value(s): I have:
Sum of All_bids_for_export.BidTotal
@MonthAvTotal

@MonthAvTotal has this formula:

Sum ({All_Bids_for_Export.BidTotal}, {All_Bids_for_Export.QuarterDate}) / 3

On the Series Options, the Month (k$) is Riser and the Quarterly Av (k$) is Area.

I need to show all the Month (k$) but for the Area part which is Quarterly Av (k$) if the month is not complete, then exclude from Quarterly Av (k$) area.

So now, the riser will show April, but the area will not show the quarter part for April/May/June as it's not complete, but now the Quarterly will show for Jan/Feb/Mar.

Does this make sense?

If you need anything else, please ask. I really appreciate your help.

Thanks,
Hayley.

 

[lbass]

You need to make {@MonthAvTotal} conditional, but I don't know what's in {All_Bids_for_Export.QuarterDate}. If it holds the last date in the quarter, then use a formula like:


if {All_Bids_for_Export.QuarterDate} < currentdate then
Sum ({All_Bids_for_Export.BidTotal}, {All_Bids_for_Export.QuarterDate}) / 3

-LB

 

[Hales008]

Hello,

The {All_Bids_for_Export.QuarterDate} holds Q1, Q2, Q3 etc.. The date field we could key on is {All_Bids_for_Export.StartDate} This is the date the document was entered. Will this work? It's not the last date in the quarter. I do not hold this information automatically.

Thanks for your patients.

 

[lbass]

You could try a formula like:

datevar x :=
(
select {All_Bids_for_Export.QuarterDate}
case "Q1" : date(year(currentdate), 3,31)
case "Q2" : date(year(currentdate),6,30)
case "Q3" : date(year(currentdate),9,30)
case "Q4" : date(year(currentdate),12,31)
);
if x < currentdate then
Sum ({All_Bids_for_Export.BidTotal}, {All_Bids_for_Export.QuarterDate}) / 3

-LB