Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gshockxcc
Creator
Creator

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

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.

View solution in original post

7 Replies
pokassov
Specialist
Specialist

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
Creator
Creator
Author

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

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


gshockxcc
Creator
Creator
Author

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

gshockxcc
Creator
Creator
Author

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

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
Creator
Creator
Author

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