Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Yes, Date comes in as 'MM/DD/YYYY' and vMaxDate is the same
Did you try quotes around your Set Modifiers?
Or=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.
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
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.
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