Hey all!

So I'm creating a new defined field for some reports and need to get the max value of a date field. I looked in the documentation and really didn't get a solid understanding of why there are 2 arguments in the MAX/MIN(value1, value2) function. All I want is a maximum value from 1 field. Why must I put 2 in the function? Ideally I want to put MAX.fieldname instead of using the function.

Can anyone shed some light on this one for me?

Thanks!

This message has been edited. Last edited by: CoolGuy,

I've always done ...

 TABLE FILE CAR
SUM MAX.RETAIL_COST
BY COUNTRY
ON TABLE PCHOLD FORMAT HTML
END 

Thanks eric.woerle,

I thought about doing that, but need the final value to be in MDYY format.

The date field I'm looking to get the max of is in YYMD format.

What I've thought up so far:

WKEND1/YYMD = IF FW EQ &FW THEN MAX(DATEFIELD, ?);
WKEND/MDYY = DATECVT(WKEND1, YYMD, MDYY);

This new defined virtual field will be used as a filter in a form for some reports that need to show the selected weekenddate's values for various fields of data for a certain group of people. This report will then let you drill down on a specific person and get trending values for the 3 weeks prior to and including the selected week end date.

Hope this additional information helps.

Thank you for your help.

CoolGuy,

I think you are misunderstanding the function. The function will evaluate a list of values, and provide the max or min of that list of values (MAX(1 ,3, 20, 5, 77, 13, 16) would return 77). For what you are doing, you just need to use MAX.DATEFIELD. Also this is only relevant in a compute. A define will only evaluate the one line, where the compute would evaluate it at the group by level. Pre versus post processing. If what you are doing is as simple as I think it is, I would evaluate the MAX in the sum verb. You also can get more complex by using a multi-verb statement. something like this:

TABLE FILE CAR
SUM MAX.RETAIL_COST 
BY COUNTRY
PRINT RETAIL_COST
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT HTML
END  

But I'm assuming thats over complicating things.

by the way,

you can change the format of a field at display time. This can be really useful for dates and numbers. So all you need to do is

 
TABLE FILE SOMETHING
SUM MAX.DATEFIELD/MDYY
BY OTHERFIELD
END 

Thanks eric.woerle,

I appreciate the far better description of the MAX() and MIN() functions that you provided versus the one that IBI has for them in the docs. Makes loads more sense.

So, for my needs, you say I would probably do something like?:

TABLE FILE x
SUM MAX.DATEFIELD
BY FW
ON TABLE PCHOLD FORMAT HTML
END

The only thing about this way of doing it is that the output is HTML. I need a new virtual define field that stores the week end date to be used later (after all is said and done) based off of present fields in a particular table. So, I need something structured like:

DEFINE FILE x
WKEND/MDYY = ...
END
TABLE FILE x
...
END

How can I get what I've requested via the report request into a new defined field?

Again, thank you for your patience, understanding & help with this. I'm still fairly new to WF8 and am trying to get better at it slowly but surely.

After some trial and error, I think I finally figured it out. I now have a define field pulling back week end dates that should be able to be used as a parameter for later uses.

Thanks again eric.woerle!

You said:

quote:

I think I finally figured it out

... So, would you please post your solution?

Thanks, Doug

quote:

Originally posted by Doug:
You said:

quote:

I think I finally figured it out

... So, would you please post your solution?

Thanks, Doug

Yes, please post... we've waited almost a year.

Doug and Squatch,

I am so sorry for not posting my solution like a year ago for this. I was super new to all this WF and forums stuff back then and didn't think to do it back then. I reread over this thread and am trying to remember what the heck I was trying to do here and am unsure what I was even thinking back then. I feel like I'm looking back at a piece of 3rd grade art I created back in the day...lol Again, I am so sorry for not doing such a year ago. I really wish I could remember what I was trying to accomplish here and for what and where but I can't. Rest assured I always post my solutions now when I do acquire such for all to review and use in their sites. I've grown up like that. lol

Ugh... What was I doing here?

Mulling around some filtering code for weekend dates, the only fex I have in production for what I assume I was working on during that time was my get_weekenddate.fex file for our retail portal.

The code is as follows:

-* This filter populates a list of week end dates for user selection.
-* It will display the most recent week end date, & all previous within given range.

-INCLUDE IBFS:/WFC/Repository/retail_reporting/retail_reports/user_filter.fex

SET HOLDLIST = PRINTONLY

-SET &TODAY = &YYMD;
-SET &THISWEEK = DATECVT((DATEADD((DATECVT(&TODAY, 'I8YYMD', 'YYMD')), 'D', 7)), 'YYMD', 'I8YYMD');
-SET &YEAR = EDIT(&YYMD,'9999$$$$');

DEFINE FILE DIMDATE
WKEND_MDYY/MDYY = DATECVT(WEEKENDING, 'YYMD', 'MDYY');
END
TABLE FILE DIMDATE
BY HIGHEST WEEKENDING
BY WKEND_MDYY
WHERE WEEKENDING LE '&THISWEEK';
WHERE FISCALYEAR GE '&YEAR';
ON TABLE PCHOLD FORMAT XML
END
-RUN

The above code may or may not be the end result of whatever I was trying to do a year ago. Hope this helps in some way.

Again, I will never NOT post my code ever again. lol Sorry guys.