maximum for not working

[MrPogle]

I have a simple list report with columns for Year, Month and then a measure called Amount. If I add a fourth column:

total([Amount] for report)

then for every row it correctly returns the grand total of all [Amount]s.

However, if I change the column or add another and make it

maximum([Amount] for report)

I do not get the maximum value. I get a number that is less than any of the monthly values though it is the same for every row.

How do I get a query to return individual months and their totals along with another column displaying the highest month's value in every row i.e. exactly what I would expect from maximum([Amount] for report)?

[oscarca]

Hey MrPogle,

total([Amount] for report) should be:
total([Amount] for [Year], [Month])
-----------------------------------------------
maximum([Amount] for report) should be:
maximum([Amount] for [Year], [Month])

Alternative to this, you could place it in the detail filters if you only use one of them:
dataitem = maximum(dataitem)

best regards,
Oscar

[MrPogle]

Thanks Oscar.

What i want to happen is for the maximum to go the other way i.e. the highest individual month of those in the query. If month 1 was 100, month 2 was 300 and month 3 was 200, I would wnat every row in the maximum column to return 300.

Doing what you suggested returns a different result for each row.

[adam_mc]

I believe that Cognos is working exactly as is expected.

What is likely happening is that for each of the rows on your output [Amount] is an aggregated column.
So, it is combining multiple rows together to get your values.
For example for your Month 1 = 100. it may actually be combining 3 rows of 30, 50, and 20 together to sum to 100.

Your maximum is returning the maximum of all these detail rows - In this example 50 not the 100 (for this row only) that you might expect.
Which as you say may very well be less than all your aggregated rows - But, in any case you will certainly results that you cannot predict from the aggregated data.
The total for report works because the sum of the detail rows is the sum of the aggregate rows - This is not true for a maximum calculation.

What you want is to get the maximum of these aggregate rows.
You may be able to get this in the same query, but typically I find it easier to pass the results of this query thru to another query.
In that query, your maximum for report calculation should work.

Hope this helps.
Thanks,
Adam

[MrPogle]

Adam - I have been looking into this more and you are absolutely correct. But... I though the whole point of the FOR expression (the for in maximum([Measure] for report) was to shift the level of aggregation up to the report level i.e. to aggregate (be it max or total or any other) there.

What I think you are saying is happening is that having, for example, for [Year] in there takes the maximum of all the values for each year and that it does so at the most granular level of data though.

This, of course, is not what I want.

A colleague of mine suggested having a total called Total Amt in a query like this:

total([Amount])

and then a second field referring to it like this:

maximum([Total Amt] for report)

The FOR function is then applied to the total([Amount]) part (total([Amount] for [Year]) etc. and this seams to work.

Thanks for your help. I guess this matter is resolved now, to the extent that it can be!