Set analysis - Date Format Issue

[spetushi]

Hello,

I have the following set analysis expression which brings relevant records but not within specified date range. I think this is due to different date formats and need help to fore came date format within the expression:

Count({$<[Report Source Type]={'Marketing'}, [Receipt Date]={'>=$(%StartDate)<=$(%EndDate)'}, [Partner Source]=>} [ReportID])

[Receipt Date] has format: 'DD-MMM-YYYY'

%StartDate and %EndDate have format: 'dd/mm/yyyy'

Can I format either date(s) in the expression itself?

Thank you,

Sokol

[swuehl]

Maybe like

[Receipt Date]={">=$(=Date(%StartDate,'DD-MMM-YYYY'))<=$(=Date(%EndDate,'DD-MMM-YYYY'))"}

Note that I've also changed the single quotes after and before {..} to double quotes.

[spetushi]

Thank you for the reply Stefan. I applied your suggestion as follows, but I am getting no results this time.

I am suspecting the issue now is not in the dates but rather with the "[Partner Source]" filter. I would like to ignore user selection against this field. Am I defining this incorrectly?

Count({$<[Report Source Type]={'Marketing'}, [Receipt Date]={">=$(=Date(%StartDate,'DD-MMM-YYYY'))<=$(=Date(%EndDate,'DD-MMM-YYYY'))"}, [Partner Source]=>}[ReportID])

Thanks in advance,

Sokol

[swuehl]

Why do you think the issue is now with the Partner Source field selection?

You can ignore all user selections by using set identifier 1 to test this:

Count({1<[Report Source Type]={'Marketing'}, [Receipt Date]={">=$(=Date(%StartDate,'DD-MMM-YYYY'))<=$(=Date(%EndDate,'DD-MMM-YYYY'))"} >}[ReportID])

Your expression should ignore selections in that field,though.

I assume that the issue is still with your date field filter. Is %StartDate and %EndDate a field with a single selected value or a variable?

[spetushi]

%StartDate and %EndDate are hidden fields used in a Calendar object for user to select.

Tried using set identifier 1 to test this and still getting no results. So, I guess issue continues to be with the dates.

[swuehl]

Does it work with hard coded date ranges in your set expression?

Why don’t my dates work?

Get the Dates Right

Dates in Set Analysis

[parul_mehta]

Can you please share the data that you are using?

[spetushi]

Something fishy with my dates. It does if I use:

-  [Receipt Date]={">=01/03/2017"}

-  but not if I use: [Receipt Date]={">=01-Mar-2017"}

Thanks for your suggestions.

[Anonymous]

This depends on your default date format:

... have a look at your script, it looks like that:

SET DateFormat='DD.MM.YYYY';

--> if the text doesn't "fit" your default format. then you you must force it like that:

date(date#('01-Mar-2017','DD-MMM-YYYY'))

or

makedate(2017,3)

A set analysis gets always text as input. If you do a numerical comparison, the number will only be "there" if it matches one of your default formats...

[swuehl · Accepted solution]

What if you try to use the numeric representation for your dates:

[Receipt Date]={">=42795"}

Or

[Receipt Date]={">=$(=Floor(Min(%StartDate)))<=$(=Floor(Max(%EndDate)))"}