Count of dates greater than 30 days old

[Anonymous]

Hi all, I'm sure this is simple but it's doing my head in...

How do I count the number of orders that have a date associated with them which is greater than 30 days old?

I've tried loads of variations of the following

count ({$<today()-30={">=$([Current Sales Cycle Activity Date])"} >} distinct [Order Number])

I need to use set analysis as I have some other conditions but just not shown them here.

Any ideas

Many thanks

David

[#2]

not tested but this will work i hope

if(Current Sales Cycle Activity Date < Today()-30, count (distinct [Order Number]))

[Anonymous]

Thanks for having a look - it didn't work. Also I need to introduce some set analysis and don't know where that would fit in this expression. I've used the if statement to get colours to change based on age of dates and that works fine but getting similar logic into an expression with set analysis is proving beyond me.

I appreciate that you took a look.

[johnca]

I would create a variable in the script:

Let v30DaysAgo = Today()-30;

Then, in your set expression...

count ({$<[Current Sales Cycle Activity Date]={'<$(=v30DaysAgo)'}>} distinct [Order Number])

Now you can add all the filters you like.

[swuehl]

When using dates in a set expression, you need to assure that date format matches. So if your field [Current Sales Cycle Activity Date] has the standard date format set in the script, try:

=count ({$<[Current Sales Cycle Activity Date] = {"<$(=Date(today()-30))"} >} distinct [Order Number])

or use an appropriate date format code with Date() function as second argument. I assume you want to count records older than 30 days (which have a date numerical value lesser  than today()-30, hence the smaller comparison)

Regards,

Stefan

edit: removed a single quote typo

[Anonymous]

I have to say this looks just like what I need and I am starting to doubt my build since I see no reason for this or indeed the other solutions not to work (yet they don't).

I think I'll have to get my consultant chap to take a look at how my dimension was pulled together as I think this may be the problem and not the expression.

[Anonymous]

Many thanks - the expression looks fine - I think I may have other issues here since all answers look correct and yet noe seem to work.

Thanks for offering a solution.

[#8]

Hi David.

I'm sorry but I used different field names but just try this way to count:

Count({<Date_Order={'<$(=date(today()-30,'DD/MM/YYYY'))'} >}Distinct [Order Number])

Let me know!!

Bye!

[patrick_forbes]

I've tried a variation of that but conflicted because I'm forced to compare it with a timestamp format. Like this:

count({$<Date(Date#(left(trim([AD_LastLogonTimestamp.autoCalendar.Date]),10),'YYYY-MM-DD'),'YYYY-MM-DD')= {"<$(=Date(today()-90))"} >} Distinct AD_Account_Name)

Can you assist with this?

Thanks!

Patrick