Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

gshockxcc
Contributor

Date range in set analysis error

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,

1 Solution

Accepted Solutions
pokassov
Valued Contributor

Re: Date range in set analysis error

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.

7 Replies
pokassov
Valued Contributor

Re: Date range in set analysis error

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 )

gshockxcc
Contributor

Re: Date range in set analysis error

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.

pokassov
Valued Contributor

Re: Date range in set analysis error

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


gshockxcc
Contributor

Re: Date range in set analysis error

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

gshockxcc
Contributor

Re: Date range in set analysis error

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.

pokassov
Valued Contributor

Re: Date range in set analysis error

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.

gshockxcc
Contributor

Re: Date range in set analysis error

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

Community Browser