Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

davidsimpson
New Contributor III

Count of dates greater than 30 days old

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

7 Replies
Not applicable

Re: Count of dates greater than 30 days old

not tested but this will work i hope

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

davidsimpson
New Contributor III

Re: Count of dates greater than 30 days old

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.

johncaqc
Valued Contributor

Re: Count of dates greater than 30 days old

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.

MVP
MVP

Re: Count of dates greater than 30 days old

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

davidsimpson
New Contributor III

Re: Count of dates greater than 30 days old

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.

davidsimpson
New Contributor III

Re: Count of dates greater than 30 days old

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.

Not applicable

Re: Count of dates greater than 30 days old

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!

Community Browser