Counting a quarter?

[knightwriter]

Hi all,

I want to count the number of cars that are sold (1) in the the 1st quarter (2) and in 2015 (3).

My dates are all days and not months so this is what Ive tried but with no luck.

Any help would be much appreciated.

=Count(Distinct If (Month([Car.Sold Date]='<=March' AND [Car.Status]='Sold' AND InYear([Car.Sold Date], Today(), 0), [Car Id], ))

[swuehl]

Best would be to create a calendar with Year, Month, Quarter in the script.

Working with your model, I think there is a date function InQuarter() (not at my system right now, so can't check in the HELP):

=Count(Distinct If ( [Car.Status]='Sold' AND InQuarter([Car.Sold Date], Yearstart(Today()), 0), [Car Id], ))

[MK_QSL]

Create Month and Quarter fields in your script

Month(Car.Sold Date) as Month,

Year(Car.Sold Date) as Year,

'Q'& Ceil(Month(Car.Sold Date)/3) as Quarter

Now usse

Count({<Quarter = {'Q1'}, Year = {'$(=Year(Today()))'}>}Distinct [Car Id])

[knightwriter]

Hi swuehl,

Many thanks for the expression. This does work as far as it gives me a number but if I wanted to use the same text box properties 4 times (ie q1,, q2, q3, q4) how would I use / create this?

[swuehl · Accepted solution]

Maybe something like

InQuarter([Car.Sold Date], Yearstart(Today()), 0)

InQuarter([Car.Sold Date], Yearstart(Today()), 1)

InQuarter([Car.Sold Date], Yearstart(Today()), 2)

InQuarter([Car.Sold Date], Yearstart(Today()), 3)