Totals by time period

[wisni1rr]

Hi again!

I have a question in regards to an approach.

I have a report that servers as a breakdown report. I need to have my report return totals based on different time criterion. For example:

Total Number of sales (Count)
Present- 3months
4-6 Months
7-12 Months

Slightly stumped on my approach. Can anyone offer advice as to how I can achieve this?

Thanks.

My current thought is something along these lines. Maybe I'm off track.
As my ControlSource

=Iff(DateDiff("m",[SoldDate],Date())<4,Count([SoldDate]))

I'm getting a parameter prompt for IIF.

[NeoPa]

Assuming you have a date field somewhere then you would need to convert that into a number of months in the past (Use DateDiff() for this). From that point you can create a field using IIf() that returns one value if the value is less than four, another if less than seven, another if less than thirteen, etc. The result of this field can then be GROUPed by and aggregated across (Count() is an aggregate function) in the usual way.

[wisni1rr]

Thank you, NeoPa. I editied my previous post. (not sure if you had the chance to view my sample code) It looks like I'm on the right track but not quite there. Is it possible/wise to do this all with one expression as I'm trying (Still debugging) or seperate it somehow?

Should I create an Expression field in my query builder for the DateDiff and call that number somehow into my ControlSource then use the iff statement there to complete my calculation?

[NeoPa]

You haven't used IIf(). You've tried to use Iff() instead.

Your follow-on question or suggestion is not a valid answer by itself, but that's already clear from my answer in post #2. Why not try going forwards from that point.

[wisni1rr]

Thanks, NeoPa.

I have added 2 expression fields to my query. The first [SoldDateDiff] is to find the DateDiff()of [SoldDate]. The second [SoldDateDiffGrp] takes [SoldDateDiff] and assigns a letter based on the monthly conditions. It is a nested IIf statement.

SoldDateDiffGrp: IIf([SoldDateDiff]<4,"A",IIf([SoldDateDiff]<7,"B",IIf([SoldDateDiff]<13,"C","D")))

I'm at a loss of how to implement the GROUP BY clause to get the desired results. Can you provide insight?

Also, These calculations are part of a report summary. The details section of the report will contain all possible situations: Current-3, 6-4, 7-12 and 13+.

[NeoPa]

You would do a GROUP BY on the expression itself, so your GROUP BY clause might look like :

GROUP BY IIf([SoldDateDiff]<4,'A',IIf([SoldDateDiff]<7,'B',IIf([SoldDateDiff]<13,'C','D')))

Although, I'd use a different expression :

Switch([SoldDateDiff]<4,'A',[SoldDateDiff]<7,'B',[SoldDateDiff]<13,'C',True,'D')

Of course, if [SoldDateDiff] is just an expression then it will not be available to the GROUP BY clause, so you'll need to make it available by providing the data in a Subquery (otherwise the full expression would be required in place of each reference to [SoldDateDiff] - Messy).

[wisni1rr]

As a clairification, a subquery is simply another query that the main query calls data from?

[NeoPa]

A subquery is a query defined within the current one, that can provide data.

It is not limited to use within the FROM clause, but can be used as criteria and even as data in the SELECT clause itself. See Subqueries in SQL for more ideas.