Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
james
Creator III
Creator III

Set Analysis for Max Date

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

7 Replies
Not applicable

Hi James,

are you compare "Date" and "vMaxDate" in the same format?

See the attached example for help.

Good luck!

Rainer

james
Creator III
Creator III
Author

Yes, Date comes in as 'MM/DD/YYYY' and vMaxDate is the same

Not applicable

Did you try quotes around your Set Modifiers?

=Avg({$<Date = {"$(=vMaxDate)"}>},Data = {'New'}>} Sales)
Or
=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
Creator III
Creator III
Author

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
Champion III
Champion III

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

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

Not applicable

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