Date Ranges issue

[TudorSmith]

Help

I need to run a totals report that spans 4 weeks. I've been given a spread sheet with derived totals

My total number of work orders for this week is '22776', last week it was '21750', the week previous was '20450' the the first week was '19033'

I realise that I need the grand total based on the current date - 7, -14 & -28, but I'm scratching my head trying to use formulas like Last7Days & Last4WeeksToSun. I'm thinking it'd be something like:

if {PMWRKORD.DATE_CREATED} in (CurrentDate)-7 to (CurrentDate) - 14 then
    count({PMWRKORD.PMWRKORD_CODE})

Any idea how I can create a forumla field that shows each of these figures?

Thanks

Tudor

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[Naith]

You're nearly there:

In your details section, place the suppressed formula:

if {PMWRKORD.DATE_CREATED} in
(CurrentDate) - 7 to
(CurrentDate) - 14
then 1
else 0

Repeat this approach for the other periods, and sum each formula.

Naith

 

[TudorSmith]

Hey...this looks good. What's a surpressed formula though?

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[TudorSmith]

I get ya!

You're giving the formual a value of 0 or 1 based on the date content...then you'll be adding the total together in the group foorter or somewhere like that eh?

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[Naith]

That's about the size of it. I mentioned suppressing the formulas only because I'm assuming you don't want to display this binary looking series of columns running down your report when these formulas are only actually needed to contribute to the final total, and don't need to be displayed at all.

If you have any trouble executing this, let us know.

Naith

 

[synapsevampire]

You might also use Running Totals with a Evaluate Use A Formula using Naith's suggested formula:

{PMWRKORD.DATE_CREATED} in
(CurrentDate) - 7 to
(CurrentDate) - 14

Sometimes they're easier to wrok with, and you don't need to suppress anything or have formulas in the details.

-k