topic Re: Set analysis - Date Format Issue in QlikView

Set analysis - Date Format Issue

spetushi — Sat, 15 Apr 2017 21:27:37 GMT

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

Re: Set analysis - Date Format Issue

swuehl — Sat, 15 Apr 2017 21:55:40 GMT

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.

Re: Set analysis - Date Format Issue

spetushi — Mon, 17 Apr 2017 12:56:23 GMT

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

Re: Set analysis - Date Format Issue

swuehl — Mon, 17 Apr 2017 13:20:16 GMT

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?

Re: Set analysis - Date Format Issue

spetushi — Mon, 17 Apr 2017 13:28:42 GMT

%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.

Re: Set analysis - Date Format Issue

swuehl — Mon, 17 Apr 2017 13:33:33 GMT

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

Re: Set analysis - Date Format Issue

parul_mehta — Mon, 17 Apr 2017 13:48:13 GMT

Can you please share the data that you are using?

Re: Set analysis - Date Format Issue

spetushi — Mon, 17 Apr 2017 13:48:53 GMT

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.

Re: Set analysis - Date Format Issue

Anonymous — Mon, 17 Apr 2017 13:55:19 GMT

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'))

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...

Re: Set analysis - Date Format Issue

swuehl — Mon, 17 Apr 2017 13:57:20 GMT

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

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

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

Re: Set analysis - Date Format Issue

spetushi — Mon, 17 Apr 2017 14:35:46 GMT

This will do it. Thanks Stefan.