Finding the maximum per expression value

[#1]

Hi,

I'm new to this forum so first of all Hello Everyone

Is there a way to find a maximum per expression value? Let me give an example. I have two dimensions: ClientName and TradeDate (dd/mm/yyyy). I want to find the maximum date per month per client, so per ClientName and month(TradeDate). I cannot make any changes to the database, so I cannot just map TradeDates into e.g. TradeMonths.

The outcome should look like that:

Client  TradeDate    MaxDateInClient&Month

ABC    10/01/2011  20/01/2011

ABC    11/01/2011  20/01/2011

ABC    20/01/2011  20/01/2011

ABC    03/02/2011 25/02/2011

ABC    04/02/2011  25/02/2011

ABC    25/02/2011  25/02/2011

Thanks in advance for any help,

Marcin

[#2]

Hello Marcin,

if you don't need every single row in your table, try this:

Create a straight table with "Client" and "Month(TradeDate)" as the two dims. The Expression is "date(max(TradeDate))".

htH

Roland

[#3]

Hi Roland,

Thanks for the quick reply but I actually need to have the TradeDate dimension in the table as well, so I actually need every single row, unfortunately.

Thanks,

Marcin

[kaushiknsolanki]

Hi,

    Have a look at the sample file.

    Hope this is what you are looking for.

Regards,

Kaushik Solanki

[#5]

Hi again,

sorry, but then I think then you are in a trap.

My assumption was, that you are not able to change the datamodell. If you want to show all rows you need a field like TradeMonth. Take a look at the post of Kaushik above.

There you find the new dim and the expression similar to my second suggestion:

"Date(max( TOTAL <TradeMonth> TradeDate))"

RR

[#6]

Hi Kaushik,

This would work great but I cannot make any changes to the database and have no influence on how it's "loaded" to QV, cannot edit the script and therefore I cannot create an additional dimension in the script called Month.

Thanks,

Marcin

[#7]

Hi Marcin,

as you are online right now, straight forward:

It Seems you need to contact the script-writer to spend you a new field "TradeMonth". This should not be to hard for her/him.

Good Luck

Roland

[#8]

Guys,

I thought it would be possible to write a formula like:

max( total <Client, Month(TradeDate)> TradeDate)

and that I just didn't know the syntax but it seems it cannot be done this way. Thanks for your time and effort.

Regards,

Marcin

[kaushiknsolanki]

Hi,

    It wont work cause you need to give only field name, you cant use the calculated field.

    Thats why it is much to have week as field.

Regards,

Kaushik Solanki

[#10]

Hello Marcin,

Kaushik is right (sorry). Take a look at this snippet out of online-help:

max( [{set_expression}] [ distinct ] [ total [<fld {, fld}>] ] expression [, rank]))

you can see that after the "total" within the "<>" are only field(-name)s valid.

RR