maximum of a number 1

[maas]

Hello All,

I am having a subreport where I am using a SQL command to retrieve the data.

The fields which are there: Deal No, Maturity Date, Deal Date, Value, comments, event number And group by Deal No.

I put a condition in the "Select Expert" to get Maturity Date <='31-12-2009'.

My problem is that, how can I get the maiximum event number when the date <='31-dec-2209'. I tried to use the maximum summary, but it gave me the wrong number. The result was the maximum value in the DB not in that period.

I defined in the Selection Formulas -> Group that
{event number}= maximum ({event number}, deal_no), but this did not work.

So, how can I get the maximum event number in a selected period.

Also, I want to filter the comments. I do not want the comments which has 'Closed'. MOst of the comments are having "Closed By XYZ". So, how can I filter to only not to display the deals which in their comments the word "Closed" at the beginning".

Thanks

 

[IanWaterman]

What data type is Maturity Date, if its a string your selection filter will not work as in a string sort

30-12-2010 is less than 31-12-2009.

Since you are using a command you should be able to return Maturity date as a truw date or change format to

YYYYMMDD ie 20091231

Then you could filter as

Maturity Date <= 20091231

To filter your comments

Not({Comments} like 'Closed*')

Ian

 

[maas]

Hello Iawan,

I am able to return the maturity date and everything is working fine except that I want to get the maximum event no for each deal in the same period.

For example:
Deal no Maturity Date Value Eventno
123 12-3-2008 111 567(this number is the maximum event no in the DB not in the period)
where the correct event no is 222

Now, my question is how to get the maximum (event no) which is less than 31-12-2009 maturity date

 

[lbass]

if {maturitydate}= maximum ({maturitydate}, {deal_no}) then
{eventno}

This would identify the row containing the maximum event number. Or you could limit the display to the particular row by using a group selection formula of:

{maturitydate}= maximum ({maturitydate}, {deal_no})

Use the following in your record selection formula:

not(ucase({comments}) like "*CLOSED*")

-LB

 

[maas]

Hello lbass,

Thanks for your response.

Just to clarify: Should I remove the condition maturity < 31-12-2009 in the select expert and define it in another place?

Because I want the maximum event number which has the proper comments for each deal which maturity date < '31-12-2009'?

SO, to deal with the issue , I need to use:
{maturitydate}= maximum ({maturitydate}, {deal_no})
and not(ucase({comments}) like "*CLOSED*")


Thanks

 

[lbass]

No, don't remove the date criterion--that establishes your basic dataset. Use something like this for your record selection formula:

Maturity Date <='31-12-2009'

Actually, I think your group selection is correct, though not what I last suggested.

-LB

 

[maas]

So, now the final formula will this:
{maturitydate}= maximum ({maturitydate}, {deal_no})
and not(ucase({comments}) like "*CLOSED*")
or I have to replace it with :
{event no}= maximum ({event no}, {deal_no})
and not(ucase({comments}) like "*CLOSED*")

Please advice?

 

[lbass]

{maturitydate}= maximum ({maturitydate}, {deal_no}) and
not(ucase({comments}) like "*CLOSED*")

-LB

 

[maas]

Hello lbass,

It did not work.

The first condition is working, but the second condition is not giving the correct result.
not(ucase({comments}) like "*CLOSED*")

It doesn't exclude the deals and remove them from the report, it is just replacing them with another comments

 

[maas]

There are some duplicated rows when I am using:
{maturitydate}= maximum ({maturitydate}, {deal_no})

I tried to use:
{event no}= maximum ({event no}, {deal_no})

It shows the correct figures as well as the comments.

But, when I am using the exclude condition
not(ucase({comments}) like "*CLOSED*")

It just replace the comments with the latest comments in the database as per the last record for each deal and also I want to remove the whole row : deal no ...etc. So, I can get the correct sum.

 

[lbass]

No, it wouldn't work like you say in your second to last post. I think you had better show the current record selection formula and the current group selection formula. Using the maximum event number will not work correctly based on how you have described the data.

You also must use running totals if you are summing across deals, not the more usual inserted summaries.

-LB

 

[maas]

Hello lbass,

This is the record selection

{DEALS.settle_DT} <= {?EndDate1} and
{DEALS.TRANS_TYPE} in ["SE"] and
{DEALS.mature_dt}> {?EndDate1} and
not ({STRUCT.NAME} in ["GA", "MM"]) and
{SECTYPE.MM_OR_SEC} in ["SECURITY"] and
{DEALS.IN_USE} = "Y" and
{@Calc_ramain_fv} <> 0.00 and
not ({MMDEALS.BUY_SELL} in ["SELL"])

and in the group selection is nothing

 

[maas]

Hello lbass,

I have tried out and got the correct values.

In each fields I went to format field and in the suppress X/2, I put a condition :

{EVENTS.COMMENTS} like ('CLOSED*')
or
{EVENTS.COMMENTS} like ('PRE*')

and in the details section I have checked the suppress blank sections.

Now, the problem is that how can I get the total sum of them and skip the suppressed, I tried using running totals and put the condition in the evaluate, but it is summing the totals with the suppressed. So, how can I get rid of them or exclude them

 

[lbass]

I'm confused about what your goal is. It sounds like you are no longer using group selection and instead are using detail suppression. But I thought you wanted to suppress entire deal groups if the most recent status was closed. Please clarify. It might help if you show two deals with sample detail level data and then show the same sample displaying as you wish.

-LB

 

[maas]

Hello lbass,

In the group selection, I used the condition which you have warned me not to use it:
{event no}= maximum ({event no}, {deal_no})
It displayed the correct figures, but now to suppress the one with the comment 'Closed' or 'Preclosed', I used the same condition which you have showed previously:
{EVENTS.COMMENTS} like ('CLOSED*')
or
{EVENTS.COMMENTS} like ('PRE*')
and place it in each deal. I clicked on every field and select format field and choose suppress (X/2) and put that condition.
Now, the deals and figures are all correct. There is a field called face value and I want to get the sum of all the face values.
I tried to use the running totals and evaluate on each deal, but it is calculating the suppressed deals and give the wrong total. So, how can I only get the calculation of the displayed face values for each deal.

 

[lbass]

You told us earlier that in some cases the maximum event number was NOT the most recent, so I'm not sure why you went that route. If you now think that the maximum event number is always the most recent, then I think you should remove the suppression criteria and instead use group selection:

{event no}= maximum ({event no}, {deal_no}) and
(
not(
{EVENTS.COMMENTS} like ('CLOSED*')or
{EVENTS.COMMENTS} like ('PRE*')
)
)

Then you can use running totals without explicitly having to remove the closed and pre rows, since group selection will allow the running totals to pick up only the displayed values. If you stick with the suppression (no good reason to do so though), you would have to explicitly remove rows with those comments by adding the above clauses to the evaluation section->use a formula.

-LB

 

[maas]

Thank you lbass