topic Re: Finding the maximum per expression value in QlikView

Finding the maximum per expression value

Fri, 07 Oct 2011 08:47:49 GMT

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

Finding the maximum per expression value

Fri, 07 Oct 2011 09:04:55 GMT

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

Finding the maximum per expression value

Fri, 07 Oct 2011 09:12:08 GMT

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

Re: Finding the maximum per expression value

kaushiknsolanki — Fri, 07 Oct 2011 09:12:20 GMT

Hi,

Have a look at the sample file.

Hope this is what you are looking for.

Regards,

Kaushik Solanki

Re: Finding the maximum per expression value

Fri, 07 Oct 2011 09:43:38 GMT

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))"

Re: Finding the maximum per expression value

Fri, 07 Oct 2011 09:43:59 GMT

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

Re: Finding the maximum per expression value

Fri, 07 Oct 2011 09:48:35 GMT

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

Re: Finding the maximum per expression value

Fri, 07 Oct 2011 10:05:18 GMT

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

Re: Finding the maximum per expression value

kaushiknsolanki — Fri, 07 Oct 2011 10:07:00 GMT

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

Re: Finding the maximum per expression value

Fri, 07 Oct 2011 12:03:51 GMT

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.