Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What if you try to use the numeric representation for your dates:
[Receipt Date]={">=42795"}
Or
[Receipt Date]={">=$(=Floor(Min(%StartDate)))<=$(=Floor(Max(%EndDate)))"}
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.
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
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?
%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.
Does it work with hard coded date ranges in your set expression?
Can you please share the data that you are using?
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.
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...
What if you try to use the numeric representation for your dates:
[Receipt Date]={">=42795"}
Or
[Receipt Date]={">=$(=Floor(Min(%StartDate)))<=$(=Floor(Max(%EndDate)))"}