topic Re: Date range in set analysis error in QlikView

Date range in set analysis error

gshockxcc — Fri, 29 May 2015 12:05:31 GMT

I have the the following expression that I'm trying to evaluate:

=Avg( {$< Date = {">=$(=Max(AddMonths(vWeek1Start,0)))<=$(=Max(AddMonths(vWeek1Start-7,0)))"}, RangeDate >} Turbo_CDEVx_A )

I have also tried an alternate expression:

=Avg( {$< Date = {">=$(=vWeek1Start)<=$(=vWeek2Start)"} >} Turbo_CDEVx_A )

I can't see to get either one of these to work properly. The result gives me the average for ALL of the dates.

I have 'vWeek1Start' defined as: =date(Today(), 'MM/DD/YYYY')

'vWeek2Start' is defined as: =Date(vWeek1Start - 7, 'MM/DD/YYYY')

I'm trying to specify the date range between "today" and 7 days prior, inclusive.

Thanks in advance,

Re: Date range in set analysis error

pokassov — Fri, 29 May 2015 12:56:04 GMT

Hello!

I think the problem is vWeek1Start>vWeek2Start and you set your 2 variable in wrong order.

Try this:

Avg( {$< Date = {">=$(=vWeek2Start)<=$(=vWeek1Start)"} >} Turbo_CDEVx_A )

Re: Date range in set analysis error

gshockxcc — Fri, 29 May 2015 13:11:00 GMT

I thought about that as well. I tried changing the order of the variables, and it fails to calculate. If I leave it as is, at least it calculates an Average value, but it does it for ALL of the data, and it does not select the date range.

Reversing the variables in the expression gives no results.

Thanks for your help.

Re: Date range in set analysis error

pokassov — Fri, 29 May 2015 13:14:32 GMT

Avg( {$< Date = {'>=$(=date(today()-7))<=$(=date(today()))'} >} Turbo_CDEVx_A )

Re: Date range in set analysis error

gshockxcc — Fri, 29 May 2015 13:26:00 GMT

I agree that the logic makes sense, but it doesn't give me a result. I have attached a sample file.

Re: Date range in set analysis error

gshockxcc — Fri, 29 May 2015 13:37:01 GMT

I noticed that you used single quotes " ' " in your expression, where I used double ". Is there a reason why one works, and the other doesn't?

I tried both, and still get no result.

Re: Date range in set analysis error

pokassov — Fri, 29 May 2015 13:52:27 GMT

Could you try this?

=Avg( {$ < Date = {">=$(=date(today()-80,'MM/DD/YYYY')) <=$(=date(date(today(),'MM/DD/YYYY')))"} > } Turbo_CDEVx_A )

1. I found the max(Date) = '4/15/2015'. It's April 15 2015. Today is almost summer )), so "May 29 2015"-7 days is bigger than "April 15 2015"

2. I found formats of date are different in script and data.

Re: Date range in set analysis error

gshockxcc — Fri, 29 May 2015 14:48:09 GMT

- Ugh, Now it makes sense. Sorry. I knew that my dates/data only went up through 4/15/2015, but I didn't make the connection when I was using the expression. You suggestion works, and now I understand why. The '-80' gets me into the date range.

Sorry for taking your time. Thanks for your help.