5 Replies Latest reply on Mar 21, 2017 11:52 AM by Justin Pulley

Count the Number of Records in the Previous week

I cannot create this as a filter, it must be built into a calculated field because I have two columns and one of them is a YTD column that would not work with a filter for this in place.

I need to come up with a formula that says COUNT([Records]) IF the [Start Date] occurred in the previous week from this week.  I tried

IF MAX([Start Date]) <  DATETRUNC('week',MAX([Start Date]))

THEN COUNT([Records])

ELSE 0

END

That doesn't seem to work.  I should have 7 records in the Previous week (3-13 through 3-19) and I get 0.

• 1. Re: Count the Number of Records in the Previous week

Can you please attach workbook ( TWBX)?

• 2. Re: Count the Number of Records in the Previous week

Hi Justin,

Find my approach based on DATEDIFF below and stored in attached workbook verion 9.3 located in the original thread.

1. calculation: if DATEDIFF('week',[date],TODAY())=1 then [value] END

Regards,

Norbert

• 3. Re: Count the Number of Records in the Previous week

The data is from a sharepoint list and there is confidential data in that list, so I cannot do a TWBX at this point.  Thanks Norbert, I will look at that.

• 4. Re: Count the Number of Records in the Previous week

Norbert, Using your formula as an example

IF DATEDIFF('week',[Start Date],TODAY(),'Monday') = 1

THEN [Number of Records]

ELSE 0

END

This is very close, it is giving me 5 records.  I'll have to play with it a bit to see why.

• 5. Re: Count the Number of Records in the Previous week

IF DATEDIFF('week',[Start Date],TODAY(),'Sunday') = 1

THEN [Number of Records]

ELSE 0

END

That yields the expected results.  Thanks for the tip!