Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
 
					
				
		
 james
		
			james
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		All, I am trying to retrieve the avg of sales for the max date loaded. I have tried
=Avg({$<Date = {$(=vMaxDate)}>},Data = {'New'}>} Sales)
No Luck
=Avg({$<Year = {$(=only(Year))},Month =,Weekend =,Date ={=$(vToday)}, Data = { 'New'}>} Sales)
No Luck
=avg({<Date={$(=today())>} Sales)
No Luck
Any Ideas??
 
					
				
		
Hi James,
are you compare "Date" and "vMaxDate" in the same format?
See the attached example for help.
Good luck!
Rainer
 
					
				
		
 james
		
			james
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Yes, Date comes in as 'MM/DD/YYYY' and vMaxDate is the same
 
					
				
		
Did you try quotes around your Set Modifiers?
=Avg({$<Date = {"$(=vMaxDate)"}>},Data = {'New'}>} Sales)=avg({<Date={"$(=today())"}>} Sales)You forgot the closing curly bracket on the Set Modifier in expression 3.
You should try putting these expressions into a table, but don't give them a label. Then when the chart is rendered, the label will be the Set Analysis expression with the dollar sign expansions evaluated. That should tell you if your Set Modifiers are producing data in the format you need.
 
					
				
		
 james
		
			james
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Yes, that's what i have done and it shows date as 12/11/2009, which is what I want, yet it doesnt reneder the avg??
I cant figure this one out at all, very odd
If I use year, month, qtr etc. i can get an answer
 
					
				
		
 johnw
		
			johnw
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Double quotes indicate a search expression instead of a literal. I haven't actually done any relevant testing, and I'm not convinced the difference is large enough to detect anyway, but I suspect that search expressions are marginally slower. So unless you explicitly need a search, such as when you're looking for greater than a specific value, I'd use single quotes. That shouldn't make any difference with whether it works or not, though.
The problem may have to do with your Date field's format. By creating a list box, I could see that sometimes it is formatted as a date, and sometimes it is formatted as a number. Set analysis isn't smart enough to convert formats when doing the comparison. I would suggest formatting all of your Dates the same and giving it another shot.
 
					
				
		
 james
		
			james
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		I have re done the enire thing with all dates ad Date(Date,'MM/DD/YYY') as Date_2 and still no luck...SO weird
 
					
				
		
Hi James,
here are my version of your example.
You mixed the format in the DATE field (12/11/2009 and other values in the format 40163). Please avoid this.
Secondly sometimes the syntax was not correct (missplacing of > or " or }).
I hope the attached example will help to reach your goal.
Good luck!
Rainer
